{"id":1840,"date":"2011-08-10T15:10:23","date_gmt":"2011-08-10T13:10:23","guid":{"rendered":"http:\/\/touk.pl\/blog\/?p=1840"},"modified":"2022-07-26T12:08:01","modified_gmt":"2022-07-26T10:08:01","slug":"przekazywanie-tablicy-rekordow-z-javy-do-procedury-skladowanej-w-oracleu","status":"publish","type":"post","link":"https:\/\/touk.pl\/blog\/2011\/08\/10\/przekazywanie-tablicy-rekordow-z-javy-do-procedury-skladowanej-w-oracleu\/","title":{"rendered":"Przekazywanie tablicy rekord\u00f3w z Javy do procedury sk\u0142adowanej w Oracle&#8217;u"},"content":{"rendered":"<p>Poni\u017cej znajduje si\u0119 opis jak przekazywa\u0107 string, tablic\u0119 string\u00f3w oraz tablic\u0119 par string\u00f3w z Javy do Oracle&#8217;a Nale\u017cy utworzy\u0107 typy w bazie danych:<\/p>\n<pre><code>create or replace type VARCHAR2_PAIR as object( k varchar2(200), v varchar2(200));\ncreate or replace type VARCHAR2_PAIRS_TABLE as table of VARCHAR2_PAIR;\ncreate or replace type VARCHAR2_TABLE as table of VARCHAR2(200);\n<\/code><\/pre>\n<p>. Potem utworzy\u0107 procedur\u0119 sk\u0142adowan\u0105<\/p>\n<pre><code>CREATE OR REPLACE PROCEDURE arrays_from_java(p_scalar varchar2\n, p_array IN VARCHAR2_TABLE\n, p_pairs_array in VARCHAR2_PAIRS_TABLE);\n<\/code><\/pre>\n<p>. A na ko\u0144cu zawo\u0142a\u0107 j\u0105 z Javy:<\/p>\n<pre>public static void callArrayProcedure() throws Exception{\n    System.out.println(\"Preparing data\");\n    String[] list1=new String[2];\n    list1[0] = \"first string\";\n    list1[1] = \"second string\";\n\n    String[][] list2=new String[2][];\n    {\n        String[] pair1 = new String[2];\n        pair1[0] = \"key1\";\n        pair1[1] = \"value1\";\n        list2[0] = pair1;\n    }\n    {\n        String[] pair2 = new String[2];\n        pair2[0] = \"key2\";\n        pair2[1] = \"value2\";\n        list2[1] = pair2;\n    }\n\n    System.out.println(\"Preparing connection\");\n    SingleConnectionDataSource scds = new SingleConnectionDataSource();\n    scds.setDriverClassName(\"oracle.jdbc.driver.OracleDriver\");\n    scds.setUrl(\"jdbc:oracle:thin:@host:1521:service_name\");\n    scds.setUsername(...);\n    scds.setPassword(...);\n    Connection conn = scds.getConnection();\n\n    System.out.println(\"Preparing array of strings\");\n    ArrayDescriptor descriptor1 = ArrayDescriptor.createDescriptor(\"VARCHAR2_TABLE\", conn );\n    ARRAY arrayToPass1 = new ARRAY( descriptor1, conn, list1 );\n\n    System.out.println(\"Preparing array of string pairs\");\n    ArrayDescriptor descriptor2 = ArrayDescriptor.createDescriptor(\"VARCHAR2_PAIRS_TABLE\", conn );\n    ARRAY arrayToPass2 = new ARRAY( descriptor2, conn, list2 );\n\n    System.out.println(\"Calling procedure\");\n    OraclePreparedStatement ps = (OraclePreparedStatement) conn.prepareStatement( \"{ call arrays_from_java( ?, ?, ? ) }\" );\n    ps.setString( 1, \"string\");\n    ps.setARRAY( 2, arrayToPass1 );\n    ps.setARRAY( 3, arrayToPass2 );\n    ps.execute();\n    ps.close();\n}\n\n.<\/pre>\n<p>Nale\u017cy te\u017c doda\u0107 do classpath orai18n.jar, gdy\u017c w przeciwnym wypadku je\u015bli baza nie b\u0119dzie mia\u0142a formatu UTF8 mo\u017ce wyst\u0119powa\u0107 b\u0142\u0105d, \u017ce stringi si\u0119 nulluj\u0105 podczas przekazywania do procedury<\/p>\n","protected":false},"excerpt":{"rendered":"Poni\u017cej znajduje si\u0119 opis jak przekazywa\u0107 string, tablic\u0119 string\u00f3w oraz tablic\u0119 par string\u00f3w z Javy do Oracle&#8217;a Nale\u017cy&hellip;\n","protected":false},"author":8,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[687,68,13],"class_list":{"0":"post-1840","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-development-design","7":"tag-db","8":"tag-java","9":"tag-oracle"},"_links":{"self":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/1840","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/comments?post=1840"}],"version-history":[{"count":22,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/1840\/revisions"}],"predecessor-version":[{"id":14291,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/1840\/revisions\/14291"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=1840"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=1840"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=1840"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}