How to map TYPE TABLE OF VARCHAR2(5) in java?

Question

I have a procedure with the signature below.

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPACK
AS
PROCEDURE GETBOX (DSSO_BoxNumber      IN     VARCHAR2,
CreateDateTime         OUT tCreateDateTime,
                          ReceiptDateTime        OUT tReceiptDateTime,
                          CSCBoxNumber           OUT tCSCBoxNumber,
                          DSSOBoxNumber          OUT tDSSOBoxNumber,
                          PackID                 OUT tPackID,
                          RequestID              OUT tRequestID,
                          ExceptionID            OUT tExceptionID,
                          Name                   OUT tName,
                          FolderID               OUT tFolderID,
                          ClosedDateTime         OUT tClosedDateTime,
                          OpenStatus             OUT tOpenStatus,
                          RequestOpenStatus      OUT tRequestOpenStatus,
                          RETURNED               OUT tRETURNED)
...

The cutom type definitions are as below.

CREATE OR REPLACE PACKAGE MYSCHEMA.MYPACK
  AS
      TYPE tCreateDateTime is TABLE of  VARCHAR2(15)
      INDEX BY BINARY_INTEGER;
      TYPE tReceiptDateTime is TABLE of VARCHAR2(15)
      INDEX BY BINARY_INTEGER;
      TYPE tCSCBoxNumber is TABLE of VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
      TYPE tDSSOBoxNumber is TABLE of  VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
      TYPE tPackID is TABLE of VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
      TYPE tRequestID is TABLE of VARCHAR2(20)
      INDEX BY BINARY_INTEGER;
      TYPE tExceptionID is TABLE of  VARCHAR2(20)
...

Can anyone please help, how to register the out parameters in java?

I have tried the following, but no luck.

cs.setString(1, "XYZ123");
cs.registerOutParameter(2, Types.ARRAY,"MYSCHEMA.MYPACK.tCreateDateTime");
...

Getting the below error.

java.sql.SQLException: invalid name pattern: MYSCHEMA.MYPACK.tCreateDateTime
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:463)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:362)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1756)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:272)
...

We have checked that the schema has access to the procedure and it is defined in the master schema. We also have tried by creating public synonym for this package. Still not working..

Looks like a very bad design when parameters like `tReceiptDateTime` are from type `VARCHAR2(15)` instead of `DATE` or `TIMESTAMP`. Anyway, apparently you assign an entire table to your procedure, maybe it would be easier to assign a `RefCursor` instead. — Wernfried Domscheit, Feb 19 '15 at 12:23
@Wernfrid: Thanks for your suggestion. I have also tried the cursor type `cs.registerOutParameter(2, OracleTypes.CURSOR,"DMD2.packcontents.tCreateDateTime");`. How to assign `RefCursor`? — Arunava Dhar, Feb 19 '15 at 12:57
I don't mean `RefCursor` for single parameter but for all parameters. Your procedure would simply look like `PROCEDURE GETBOX (DSSO_BoxNumber IN VARCHAR2, cur OUT SYS_REFCURSOR) IS ...` and Java call is like `cs.registerOutParameter(2, OracleTypes.CURSOR);`. Check this page for example [JDBC CallableStatement – Stored Procedure CURSOR example](http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-cursor-example/) — Wernfried Domscheit, Feb 19 '15 at 13:27

score 4 · Answer 1 · answered Mar 01 '16 at 20:47

I was able to map a type table of varchar2 to java as follows:

Create a new type outside of any PLSQL package and grant necessary permissions.

 CREATE OR REPLACE TYPE STRARRAY AS TABLE OF VARCHAR2 (100);
 /
 GRANT all ON MYSCHEMA.STRARRAY TO MYUSER1;
 /
 commit;

Create a PLSQL function that accepts/returns the strarray. This was declared in the package specification and written in full in the package body. Although I declared the array to be IN OUT, in my implementation I only actually use the output from the PLSQL call.
```
PROCEDURE getArr(arr_var IN OUT strarray) IS
  counter NUMBER := 1;
BEGIN
  arr_var := new strarray();
  WHILE counter <= 10 LOOP
    arr_var.extend();
    arr_var(counter) := 'my data string';
  END LOOP;
END getArr;
```

Call the procedure in java. In this example the variable conn is of data type Connection and has already been initialized. I am running a jdbc thin client against an Oracle database.

CallableStatement proc = null;
String sql = "{ call myPackage.getArr(?) }";
try{
  proc = conn.prepareCall(sql);
  proc.registerOutParameter(1, OracleTypes.Array, "MYSCHEMA.STRARRAY");
  proc.execute();
  Array arrOut = proc.getArray(1);
  for (int num=0; num<10; num++){
     System.out.println(arrOut[num]);
}finally{
  proc.close();
}

score 2 · Accepted Answer · edited May 23 '17 at 11:50

The title of your question is misleading. table of <TYPE> and table of <TYPE> index by <TYPE> are two very different data types. First is called nested table and second is called associative array in Oracle (PL/)SQL parlance.

The main problems are that:

the collection types used in PL/SQL interfaces revealed to Java needs to be SQL types, not PL/SQL types
an associative array is not a SQL type but a PL/SQL type

The first issue is addressed e.g. in How to return an array from Java to PL/SQL? (the issue is the same even the call direction is different).

How to map TYPE TABLE OF VARCHAR2(5) in java?

2 Answers2

Linked