Oracle call A stored procedure from JDBC with a complex input and output type

Question

I understand this issue so closely, but I obviously missed something. My requirement is to call the stored procedure in Oracle from JDBC. The stored procedure accepts 1 Oracle user object as INput and another Oracle user object as OUTput. INput and OUTput objects combine both Oracle primitive data types and a collection of another set of user objects. I can successfully call the stored procedure and return the results while I set NULL for collection types in INput and OUTput objects. If I try to create an ArrayDescriptor for a list of Oracle objects to send it to a stored procedure, I continue to beat the roadblocks. So I need help on how to set an Array to an INput object and set it to CallableStatement. Please note: I know how I can send a primitive type and array as direct entries to a stored procedure. But I do not want to go the way we later have to send 10 additional fields to the procedure, I do not want to add them to the method signature. Here is a list of classes. In addition, there are no compilation errors for the code below.

Package in oracle:

CREATE OR REPLACE PACKAGE testPkg AS PROCEDURE spGetTestData ( TESTDATA_IN IN TESTDATA_IN_OBJ, TESTDATA_OUT OUT TESTDATA_OUT_OBJ ); END; 

INput object for the stored procedure :

 CREATE OR REPLACE TYPE TESTDATA_IN_OBJ AS OBJECT( testStr1 VARCHAR2(5), arrObj1 ARR_OBJ_1_NT); 

An array object as part of an INput object :

 create or replace TYPE ARR_OBJ_1_NT AS TABLE OF ARR_OBJ_1_OBJ; 

UserDefined Object of the INput Object :

 CREATE OR REPLACE TYPE ARR_OBJ_1_OBJ AS OBJECT ( teststr VARCHAR2(14), testNumber NUMBER(4), ); 

TestDataINObj.java:

  import java.sql.Array; import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput; public class TestDataINObj implements SQLData { private String sql_type = "TESTDATA_IN_OBJ"; protected String testStr1; protected Array arrObj1; @Override public String getSQLTypeName() throws SQLException { return this.sql_type; } // getter and setter for fields @Override public void readSQL(SQLInput stream, String typeName) throws SQLException { this.sql_type=typeName; this.testStr1 = stream.readString(); this.arrObj1 = stream.readArray(); } @Override public void writeSQL(SQLOutput stream) throws SQLException { stream.writeString(this.testStr1); stream.writeArray(this.arrObj1); } } 

TestDataINObjConverter.java

  public class TestDataINObjConverter { public static TestDataINObj convertPOJOToDBInObj(Connection connection) throws SQLException { TestDataINObj testDataINObj = new TestDataINObj(); testDataINObj.setTestStr1("some string"); ArrObj1NT[] ArrObj1NTList = ArrObj1NTConverter.convertPOJOToDBObj(); // this will return Java array of ArrObj1NT class testDataINObj.setArrObj1(getOracleArray("ARR_OBJ_1_NT",connection, ArrObj1NTList)); return testDataINObj; } private static Array getOracleArray(final String typeName, Connection connection, ArrObj1NT[] ArrObj1NTList) throws SQLException { if (typeName == null) { return null; } Array oracleArray = new ARRAY(new ArrayDescriptor(typeName, connection), connection, ArrObj1NTList); return oracleArray; } 

The code that actually makes the stored procedure call :

  ... //code to get connection ..// connection is of type T4CConnection Map typeMap = connection.getTypeMap(); typeMap.put("TESTDATA_IN_OBJ", TestDataINObj.class); typeMap.put("TESTDATA_OUT_OBJ", TestDataOUTObj.class); typeMap.put("ARR_OBJ_1_NT", ArrObj1NT.class); TestDataINObj testDataINObj = TestDataINObjConverter.convertPOJOToDBInObj(connection); getMetaDataCallableStatement = connection.prepareCall("begin " + "testPkg" + ".spGetTestData (?,?);"+ " end;"); getMetaDataCallableStatement.setObject(1, testDataINObj); getMetaDataCallableStatement.registerOutParameter(2, Types.STRUCT, "TESTDATA_OUT_OBJ"); rs = getMetaDataCallableStatement.executeQuery(); TestDataOUTObj testDataOUTObj = (TestDataOUTObj) getMetaDataCallableStatement.getObject(2, typeMap); 

Miscellaneous : 1. Objects are declared at the Schema level and are available to the db user to access it. 2. I have not included all the relevant Java objects here, as this will take up more space. They implement the SQLData interface, and their type names correspond to the database names. The read and writeSQL methods use getString, getArray and the corresponding tuning methods.

Answer 1

This is a very old approach, why aren't you using the Oradata and Oradatum interface? This will save a ton of effort.

Your approach leaves a lot of scopr for error, you will need to read the stream in the proper manner and independently check the order of the fields, which can be complicated. The Oradata approach will do it for you.

Approaching your approach, your code is not very clear. But just to give an overview, StructDescriptor will display the type of oracle record, and ArrayDescriptor will display in the form of an oracle table, from your code I'm confused about what you are trying to achieve.

I can help if you can make it more clear.