Przekazywanie tablicy rekordów z Javy do procedury składowanej w Oracle’u

Poniżej znajduje się opis jak przekazywać string, tablicę stringów oraz tablicę par stringów z Javy do Oracle’a Należy utworzyć typy w bazie danych:

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);

. Potem utworzyć procedurę składowaną

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

. A na końcu zawołać ją z Javy:

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();
}

.

Należy też dodać do classpath orai18n.jar, gdyż w przeciwnym wypadku jeśli baza nie będzie miała formatu UTF8 może występować błąd, że stringi się nullują podczas przekazywania do procedury

You May Also Like

Mock Retrofit using Dagger and Mockito

Retrofit is one of the most popular REST client for Android, if you never use it, it is high time to start. There are a lot of articles and tutorial talking about Retrofit. I just would like to show how to mock a REST server during develop of app and i...Retrofit is one of the most popular REST client for Android, if you never use it, it is high time to start. There are a lot of articles and tutorial talking about Retrofit. I just would like to show how to mock a REST server during develop of app and i...