Call Oracle Stored procedure from JDBC with complex Input and Output type

Question

I'm so close in solving this question but I'm apparently missing something. My requirement is to call a stored procedure in Oracle from JDBC. The stored procedure takes 1 user-defined Oracle object as INput and another user-defined Oracle object as OUTput. The INput and OUTput objects have mix of both primitive Oracle data types and collection of another set of user-defined objects. I'm successfully able to call the stored procedure and get results back as long as I set NULL for the collection types in the INput and OUTput objects. If I try to create ArrayDescriptor for the list of Oracle objects to send it to the stored procedure I keep hitting roadblocks. So I need help with figuring out how to set the Array to the INput object and set that to CallableStatement. Please note, I am aware of how I can send the primitive type and array as direct inputs to the stored procedure. But I do not want to go that way as we later have to send 10 additional fields to the procedure, I do not want to add them to method signature. Here's the list of classes. Also, there is 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);

Array Object as part of INput Object:

create or replace TYPE      ARR_OBJ_1_NT AS TABLE OF ARR_OBJ_1_OBJ;

UserDefined Object part of 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;
}

Code that actually executes call to stored procedure:

    ... //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. The objects are declared in Schema level and is available for the db user to access it. 2. I've not included all of the corresponding Java objects here as it will take more space. They implement SQLData interface and their type names match with DB names. The read and writeSQL methods uses getString, getArray and corresponding setter methods.

Answer 1

This is a very old approach, why are you not using "Oradata" and "Oradatum" interface? It will save lot of effort.

Your approach leaves a lot of scopr for error, you will have to read the stream in proper manner and check for ordering of fields yourself which can be tricky. Oradata approach will do that for you.

Coming to your approach, Your code is not very clear. But just to give an overview, StructDescriptor will map to oracle record type and ArrayDescriptor will map to oracle table type, from your code i am confused about whta you are trying to achieve.

I can help if you can make it more clear.