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

Grails with Spock unit test + IntelliJ IDEA = No thread-bound request found

During my work with Grails project using Spock test in IntelliJ IDEA I've encountered this error:

java.lang.IllegalStateException: No thread-bound request found: Are you referring to request attributes outside of an actual web request, or processing a request outside of the originally receiving thread? If you are actually operating within a web request and still receive this message, your code is probably running outside of DispatcherServlet/DispatcherPortlet: In this case, use RequestContextListener or RequestContextFilter to expose the current request.
at org.springframework.web.context.request.RequestContextHolder.currentRequestAttributes(RequestContextHolder.java:131)
at org.codehaus.groovy.grails.plugins.web.api.CommonWebApi.currentRequestAttributes(CommonWebApi.java:205)
at org.codehaus.groovy.grails.plugins.web.api.CommonWebApi.getParams(CommonWebApi.java:65)
... // and few more lines of stacktrace ;)

It occurred when I tried to debug one of test from IDEA level. What is interesting, this error does not happen when I'm running all test using grails test-app for instance.

So what was the issue? With little of reading and tip from Tomek Kalkosiński (http://refaktor.blogspot.com/) it turned out that our test was missing @TestFor annotation and adding it solved all problems.

This annotation, according to Grails docs (link), indicates Spock what class is being tested and implicitly creates field with given type in test class. It is somehow strange as problematic test had explicitly and "manually" created field with proper controller type. Maybe there is a problem with mocking servlet requests?