Retrieving data from associative array of custom object type from Oracle DB using CallableStatement

Question

Summary

I am calling a stored procedure(SP) in an Oracle 12c database from a Spring-boot application. I am using CallableStatement to do this. One of the OUT parameters of the SP is an associative array with elements of a custom object type. I am having trouble retrieving the data in a proper method.

Code

TYPE trans_list IS TABLE OF T_RPT_TXN_DTLS_OBJ INDEX BY PLS_INTEGER;
TYPE T_RPT_TXN_DTLS_OBJ AS OBJECT( id VARCHAR2(20), amount NUMBER(10,2), desc VARCHAR2(100))

Connection connection = jdbcTemplate.getDataSource().getConnection();
OracleCallableStatement callableStatement = connection.prepareCall("{call SAMPLE_PROC(?,?)}").unwrap(OracleCallableStatement.class);
callableStatement.setString(1, ID); 

--Here I don't know what to do--
callableStatement.registerIndexTableOutParameter(2, 500, OracleTypes.OTHER, 0);
--------------------------------

Things I have tried

I looked through different websites using google. Went through stackoverflow as well. The above code is where I am stuck at. Since the 3rd argument is the datatype of the array elements, I put in OracleTypes.OTHER but that throws an SQLException: invalid column type 1111.
I read in the official docs that for custom object types, we can map them to Java objects. I have no idea how to do that either. Please refer to User_defined types in https://docs.oracle.com/cd/B19306_01/java.102/b14355/datacc.htm#BHCGCBJC

What I am asking in brief

Any sample code, detailed docmentation, links to online examples that fits my case. I specifically need to know arg[2] of the out parameter registration, and how I might go about mapping to a custom Java object from the callableStatement (perhaps using .getObject(int) but I don't know about the mapping).

@RaviSamani yes, I did. it raises "java.sql.SQLException: Invalid PL/SQL Index Table element type". — Tanzim Kabir, Feb 24 '21 at 07:30

score 0 · Answer 1 · answered Feb 24 '21 at 08:05

0

Define:

callableStatement.registerOutParameter(2, OracleTypes.ARRAY,"trans_list");

Call proc:

callableStatement.executeUpdate();

Then get out parameter as

   java.sql.Array array = callableStatement.getArray(2);
   Object outputParamValue = array.getArray();

Type Caste outputParamValue if needed.

answered Feb 24 '21 at 08:05

RaviSam

130
1
8

I have tried this. I keep getting the error "java.sql.SQLException: Internal Error: Image is not in 8.1 format" – Tanzim Kabir Feb 24 '21 at 08:33

score 0 · Accepted Answer · answered Feb 24 '21 at 09:51

See this answer.

You cannot.

You need to define the data type in the SQL scope (using CREATE TYPE) rather than in the PL/SQL scope, in a package as JDBC can only work with SQL defined data types.
A consequence of point 1 is that JDBC does not support associative arrays, as they are a PL/SQL only data type, and you need to use collections (unlike C#, which only supports associative arrays and does not support collections). So you need to remove the INDEX BY clause from the type.
You need to be able to map from the type in the array to a Java data structure; one way of doing this is to use the SQLData interface. An example is in this answer.

Retrieving data from associative array of custom object type from Oracle DB using CallableStatement

Summary

Code

Things I have tried

What I am asking in brief

2 Answers2

Linked