{"id":1862,"date":"2011-08-10T16:33:45","date_gmt":"2011-08-10T14:33:45","guid":{"rendered":"http:\/\/touk.pl\/blog\/?p=1862"},"modified":"2023-03-23T11:05:00","modified_gmt":"2023-03-23T10:05:00","slug":"passing-arguments-from-java-to-oracle-stored-procedure","status":"publish","type":"post","link":"https:\/\/touk.pl\/blog\/2011\/08\/10\/passing-arguments-from-java-to-oracle-stored-procedure\/","title":{"rendered":"passing arguments from Java to Oracle stored procedure"},"content":{"rendered":"<p>This is an example about how to pass string, string array or string pairs array from Javy to Oracle You should create the following types in your database:<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">create or replace type VARCHAR2_PAIR as object( k varchar2(200), v varchar2(200));\r\ncreate or replace type VARCHAR2_PAIRS_TABLE as table of VARCHAR2_PAIR;\r\ncreate or replace type VARCHAR2_TABLE as table of VARCHAR2(200);\r\n<\/pre>\n<p>Then you should create stored procedure of the following signature<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">CREATE OR REPLACE PROCEDURE arrays_from_java(p_scalar varchar2\r\n, p_array IN VARCHAR2_TABLE\r\n, p_pairs_array in VARCHAR2_PAIRS_TABLE);<\/pre>\n<p>And in the end call it from Java<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">public static void callArrayProcedure() throws Exception{\r\n    System.out.println(\"Preparing data\");\r\n    String[] list1=new String[2];\r\n    list1[0] = \"first string\";\r\n    list1[1] = \"second string\";\r\n\r\n    String[][] list2=new String[2][];\r\n    {\r\n        String[] pair1 = new String[2];\r\n        pair1[0] = \"key1\";\r\n        pair1[1] = \"value1\";\r\n        list2[0] = pair1;\r\n    }\r\n    {\r\n        String[] pair2 = new String[2];\r\n        pair2[0] = \"key2\";\r\n        pair2[1] = \"value2\";\r\n        list2[1] = pair2;\r\n    }\r\n\r\n    System.out.println(\"Preparing connection\");\r\n    SingleConnectionDataSource scds = new SingleConnectionDataSource();\r\n    scds.setDriverClassName(\"oracle.jdbc.driver.OracleDriver\");\r\n    scds.setUrl(\"jdbc:oracle:thin:@host:1521:service_name\");\r\n    scds.setUsername(...);\r\n    scds.setPassword(...);\r\n    Connection conn = scds.getConnection();\r\n\r\n    System.out.println(\"Preparing array of strings\");\r\n    ArrayDescriptor descriptor1 = ArrayDescriptor.createDescriptor(\"VARCHAR2_TABLE\", conn );\r\n    ARRAY arrayToPass1 = new ARRAY( descriptor1, conn, list1 );\r\n\r\n    System.out.println(\"Preparing array of string pairs\");\r\n    ArrayDescriptor descriptor2 = ArrayDescriptor.createDescriptor(\"VARCHAR2_PAIRS_TABLE\", conn );\r\n    ARRAY arrayToPass2 = new ARRAY( descriptor2, conn, list2 );\r\n\r\n    System.out.println(\"Calling procedure\");\r\n    OraclePreparedStatement ps = (OraclePreparedStatement) conn.prepareStatement( \"{ call arrays_from_java( ?, ?, ? ) }\" );\r\n    ps.setString( 1, \"string\");\r\n    ps.setARRAY( 2, arrayToPass1 );\r\n    ps.setARRAY( 3, arrayToPass2 );\r\n    ps.execute();\r\n    ps.close();\r\n}\r\n<\/pre>\n<p>You should also add orai18n.jar to Java classpath, otherwise strings may become null after passing them to stored procedure if the basic character set (NLS_CHARACTERSET) is not UTF<\/p>\n","protected":false},"excerpt":{"rendered":"This is an example about how to pass string, string array or string pairs array from Javy to&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-1862","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\/1862","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=1862"}],"version-history":[{"count":7,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/1862\/revisions"}],"predecessor-version":[{"id":9358,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/1862\/revisions\/9358"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=1862"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=1862"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=1862"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}