CODEWITHSUNDEEP
jdbcplsqlojdbc
Cosmin D
Cosmin D

Reputation: 729

Oracle JDBC call PL/SQL procedure with input parameter a table of records

I am trying to call the following pl/sql procedure from JDBC.

create or replace PACKAGE test AS

type testrec_r is record (
 val1 number,
 val2 varchar2(100)
 );

 type testarr_t is table of testrec_r index by binary_integer;

function test_func(i_data in testarr_t, o_sum out number, o_totallength out     number) return number;

END test;

This is how I tried to invoke it, but without success:

StructDescriptor recDescriptor = StructDescriptor.createDescriptor("test.testrec_r", conn);
    STRUCT[] RECORDS_ARRAY = new STRUCT[2];

    for (int i = 0; i < 2; i++) {
         STRUCT oracle_record = new STRUCT(recDescriptor, conn, new
         Object[] {i, "test"});
         RECORDS_ARRAY[i] = oracle_record;
    }

    CallableStatement stmt = conn.prepareCall("{ call TEST.TEST_FUNC(?, ?, ?) }");

    ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("TEST.TESTARR_T", conn);
    ARRAY oracle_array = new ARRAY(arrayDescriptor, conn, RECORDS_ARRAY);

    // Bind the input record
    stmt.setArray(1, oracle_array);
    stmt.registerOutParameter(2, Types.NUMERIC);
    stmt.registerOutParameter(3, Types.NUMERIC);
    stmt.executeUpdate();
    double out1 = stmt.getDouble(2);
    double out2 = stmt.getDouble(3);
    return new Object[] { out1, out2 };

I just have read that oracle jdbc does not support pl/sql struct types. So, this fails with "invalid name pattern: test.testrec_r"

How can I call this procedure from Java? Ideally would be to only use a java libray/API, but as this seems almost imposible, which is the best workaround to wrap the pl/sql package in simple sql call and to invoke it?

P.S I am using Spring JDBCTemplate for database connection.

Upvotes: 1

Views: 2928

Answers (1)

0xdb
0xdb

Reputation: 3697

You cannot use PL/SQL types because they known to PL/SQL alone (since 12c this is no more strictly true - see UPD). Also any type created within a package is not visible by java directly.
You should create a SQL type at schema level. SQL types are visible to all and usable by all.

create or replace and compile java source named "ArrayOfRecTest" as 
import java.io.*; 
import java.sql.*; 
import oracle.sql.*; 
import oracle.jdbc.driver.*; 

public class ArrayOfRecTest 
{ 
    public static void passArrayOfRec() throws SQLException 
    { 
        Connection conn = new OracleDriver().defaultConnection(); 

        StructDescriptor sd = StructDescriptor.createDescriptor("MYREC_TYPE", conn);
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("MYRECARR_TYPE", conn);
        STRUCT[] recarr = new STRUCT[2];
        for (int i = 0; i < 2; i++) { recarr[i] = new STRUCT(sd, conn, new Object[] {i+1, "value " + (i+1)}); }
        ARRAY oracle_array = new ARRAY(ad, conn, recarr);

        CallableStatement stmt = conn.prepareCall("{ ? = call testpkg.showArrOfRec(?, ?, ?) }");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setObject(2, oracle_array);
        stmt.registerOutParameter(3, Types.INTEGER);
        stmt.registerOutParameter(4, Types.INTEGER);

        stmt.execute();
        int sizeofArr = stmt.getInt(1);
        int total = stmt.getInt(3);
        int totalLength = stmt.getInt(4);
        System.out.println("passArrayOfRec(total,len)=(" + total + "," + totalLength + ") " + sizeofArr + " records were shown");
    }
}
/

create or replace force type myrec_type as object( id number, value varchar2(100));
/ 
create or replace type myrecarr_type as table of myrec_type;
/

create or replace package testpkg as
    procedure passArrayOfRec as language java name 'ArrayOfRecTest.passArrayOfRec()' ; 
    function showArrOfRec(ra myrecarr_type, total out number, totallength out number) return number;
end testpkg;
/

create or replace package body testpkg as 
    --OP stuff 
    type testrec_r is record (val1 number, val2 varchar2(100));
    type testarr_t is table of testrec_r index by binary_integer;
    function test_func(data in testarr_t, total out number, totallength out number) return number is
    begin
        <<for_each>> for i in data.first..data.last loop
            dbms_output.put_line('data(' || i || ')[val1,val2]=[' || data(i).val1 || ',' || data(i).val2 || ']');
            total := nvl(total,0) + data(i).val1;
            totallength := nvl(totallength,0) + length(data(i).val2);
        end loop for_each;
        return data.count;
    end test_func;
    --end OP stuff

    function showArrOfRec(ra myrecarr_type, total out number, totallength out number) return number is
        data testarr_t; 
    begin
        for i in ra.first..ra.last loop data(i).val1 := ra(i).id; data(i).val2 := ra(i).value; end loop; 
        return test_func(data, total, totalLength);
    end showArrOfRec;
end testpkg;
/

exec testpkg.passArrayOfRec;

Output:

data(1)[val1,val2]=[1,value 1]
data(2)[val1,val2]=[2,value 2]
passArrayOfRec(total,len)=(3,14) 2 records were shown

UPD New in 12cR1: Using PL/SQL Types

Upvotes: 2

Related Questions