passing arguments from Java to Oracle stored procedure

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:

create or replace type VARCHAR2_PAIR as object( k varchar2(200), v varchar2(200));
create or replace type VARCHAR2_PAIRS_TABLE as table of VARCHAR2_PAIR;
create or replace type VARCHAR2_TABLE as table of VARCHAR2(200);

Then you should create stored procedure of the following signature

CREATE OR REPLACE PROCEDURE arrays_from_java(p_scalar varchar2
, p_array IN VARCHAR2_TABLE
, p_pairs_array in VARCHAR2_PAIRS_TABLE);

And in the end call it from Java

public static void callArrayProcedure() throws Exception{
    System.out.println("Preparing data");
    String[] list1=new String[2];
    list1[0] = "first string";
    list1[1] = "second string";

    String[][] list2=new String[2][];
    {
        String[] pair1 = new String[2];
        pair1[0] = "key1";
        pair1[1] = "value1";
        list2[0] = pair1;
    }
    {
        String[] pair2 = new String[2];
        pair2[0] = "key2";
        pair2[1] = "value2";
        list2[1] = pair2;
    }

    System.out.println("Preparing connection");
    SingleConnectionDataSource scds = new SingleConnectionDataSource();
    scds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
    scds.setUrl("jdbc:oracle:thin:@host:1521:service_name");
    scds.setUsername(...);
    scds.setPassword(...);
    Connection conn = scds.getConnection();

    System.out.println("Preparing array of strings");
    ArrayDescriptor descriptor1 = ArrayDescriptor.createDescriptor("VARCHAR2_TABLE", conn );
    ARRAY arrayToPass1 = new ARRAY( descriptor1, conn, list1 );

    System.out.println("Preparing array of string pairs");
    ArrayDescriptor descriptor2 = ArrayDescriptor.createDescriptor("VARCHAR2_PAIRS_TABLE", conn );
    ARRAY arrayToPass2 = new ARRAY( descriptor2, conn, list2 );

    System.out.println("Calling procedure");
    OraclePreparedStatement ps = (OraclePreparedStatement) conn.prepareStatement( "{ call arrays_from_java( ?, ?, ? ) }" );
    ps.setString( 1, "string");
    ps.setARRAY( 2, arrayToPass1 );
    ps.setARRAY( 3, arrayToPass2 );
    ps.execute();
    ps.close();
}

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

You May Also Like

Context menu or Action buttons ?

Recently I was drawn into one of those UI "religious" disputes that has no easy answers and usually both sides are right. One of our web developers was trying out new web tech (with pretty rich widget library) and started to question himself about some basic usability decisions. The low level problem in this case is usually brought to "which widget should I use ?". I'm not fond of bringing the usability problems to questions: Should I use Tabs over Menu ? Or should I use Context menu instead of buttons panel ? But sometimes if time is crucial factor and other usability levels are by default not addressed at all - better developer that asks those basic questions than developer that do not question himself at all.