Accessing types defined inside package from java

Question

My procedure in oracle is defined under a package, which has in and out parameters of table of records. I can call the procedure using packagename.procname using the callable statement from java. However to access the out parameter, I need to define a variable of type ARRAY. But the types of record and table of records in oracle are defined inside the package. Thus those types are not accessible from java using the ARRAYDESCRIPTOR.

The proc and types are defined as below:

CREATE OR REPLACE PACKAGE mypackage IS
TYPE TY_Pos IS RECORD
(        cust_id     VARCHAR2(9)

,        balance NUMBER
);

TYPE TY_TBL IS TABLE OF TY_Pos INDEX BY PLS_INTEGER;

PROCEDURE myproc(inTable IN OUT     TY_TBL,
                                      count IN NUMBER,
                                      outTable         IN OUT     TY_TBL
                                      );

CREATE OR REPLACE PACKAGE BODY mypackage AS

PROCEDURE myproc(inTable IN OUT     TY_TBL,
                                      count IN NUMBER,
                                      outTable         IN OUT     TY_TBL
                                      ) as 
--proc body

For accessing the out variable of proc which is a table of records, I am creating array descriptor as

ArrayDescriptor myDescp = ArrayDescriptor.createDescriptor ("TY_TBL", l_con);

But since the TY_TBL is defined inside the package thus it throws error. Please help me how can I access this type from my java code.

It gives sql exception, java.sql.SQLException: invalid name pattern. And yes it is the same exception thrown appending the package name. I am not sure if a refcursor can be used to access the table. I have used ArrayDescriptor to fetch the object which is defined as a table of object. — Ashish Nagar, Oct 25 '12 at 10:56
[PL/SQL types are invisible to java](http://stackoverflow.com/questions/1399750/java-passing-array-in-oracle-stored-procedure). You can use SQL types (CREATE TYPE...) though. — Vincent Malgrat, Oct 25 '12 at 11:33
@VincentMalgrat, if that so, then what should I do to read the output? I want to group all these (procedure, types) into a package. — Ashish Nagar, Oct 25 '12 at 11:48
I thought it was impossible to access PLSQL arrays from jdbc but there may have been some improvements in the recent versions: see [Creating package-level associative array in java](http://stackoverflow.com/a/458090/119634) — Vincent Malgrat, Oct 25 '12 at 12:59
The conclusion I found from the link is "Oracle JDBC does not support RAW, DATE, and PL/SQL RECORD as element types" Does that mean I won't be able to access it from java as the object I want to access from the procedure is of TABLE of RECORD type? — Ashish Nagar, Oct 26 '12 at 06:53
I don't think table of PLSQL records are supported. [Table of SQL objects are OK though](http://stackoverflow.com/questions/3626061/how-to-call-oracle-stored-procedure-which-include-user-defined-type-in-java). — Vincent Malgrat, Oct 26 '12 at 06:58
Defining an Object type is an sql statement and thus cannot be put inside the package. So, creating a Table of Object would require my Object to be defined globally. In that way I won't be able to group it into the package. — Ashish Nagar, Oct 26 '12 at 07:06
This is not a technical impossibility anymore :) merely an inconvenience — Vincent Malgrat, Oct 26 '12 at 07:10

score 2 · Answer 1 · answered Jan 07 '16 at 20:46

TY_TBL is a Pl/SQL type that means, this type is invisible to java.

I suggest that create two sql types so:

 Create or Replace SQL_TY_Pos TYPE AS OBJECT (cust_id     VARCHAR2(9), balance NUMBER);
 Create or Replace SQL_TY_TBL TYPE IS TABLE OF SQL_TY_Pos INDEX BY PLS_INTEGER;

And then in java:

ArrayDescriptor myDescp = ArrayDescriptor.createDescriptor ("SQL_TY_TBL", l_con);

score 1 · Answer 2 · answered Sep 23 '14 at 07:44

it's not a very clean solution, but it works

        StringBuilder sql = new StringBuilder("DECLARE\n\tarr OTHERPACKAGE.TYPE;\n");
    sql.append("BEGIN\n");
    for (int i = 0; i < tmpArray.length; i++) {
        sql.append("\tarr(").append(i).append(") := '").append(tmpArray[i]).append("';\n");
    }
    sql.append("\tSCHEMA.PACKAGE.PROC(arr, ?);\n");
    sql.append("END;");

    Connection conn;
    CallableStatement callableStatement = conn.prepareCall(sql.toString());
    callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
    callableStatement.execute();

Accessing types defined inside package from java

2 Answers2

Linked