Su Chikki

hi Shriyans
I'm trying to do a similar thing and facing almost similar issue. My objects are defined in schema level. My stored procedure takes only one object as input and one object as output. The input object has set of Strings and an array of another DB Obj. I'm able to call the stored procedure if I set null for the array in the INput object. I still haven't figured how to set the array in the INput object. I've almost exhausted the forum discussions on this topic but nobody have talked about my situation. Please note, I'm not setting the array directly to the CallableStatement as you do but I want to set it to a SQLData object and send that to the stored procedure. If anybody can just show how it can be done it would be great. Or atleast a small nudge in that direction is all I need.

P.S: Here's my original post on the above question: https://coderanch.com/t/566654/JDBC/java/Stored-Procedure-Java

Thanks
S Chikki

Thanks for the link. I've followed this sample, along with many I found, to implement what I have now. As you can see, I'm doing the exact same thing for creating the oracle.sql.ARRAY. The difference in the example link is that, they set the created ARRAY directly to the CallableStatement to be executed. In my case, I have to set it as part of an Object which will be the only input to the stored procedure. So my question is, how can I set the Java Array to SqlType array in an Object which implements SQLData? I've included the code in my question.

Hi guys
I need help with the following issue. I'm writing a web application which have to call a stored procedure using JDBC with IN object and receive output as OUT object. Here is the jist of the objects I have.

TestPkg in Oracle and the stored procedure I'm going to call from Java:

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

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


ARR_OBJ_1_NT create or replace TYPE ARR_OBJ_1_NT AS TABLE OF ARR_OBJ_1_OBJ;

ARR_OBJ_1_OBJ
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); } }


ArrObj1NT.java:
public class ArrObj1NT implements SQLData { private String sql_type = "LTM_IN_NT"; protected String testStr1; protected double testNumber1; // constructor with parameter // getter and setter for fields @Override public String getSQLTypeName() throws SQLException { return this.sql_type; } @Override public void readSQL(SQLInput stream, String typeName) throws SQLException { this.sql_type=typeName; this.testStr1 = stream.readString(); this.testNumber1 = stream.readDouble(); } @Override public void writeSQL(SQLOutput stream) throws SQLException { stream.writeString(this.testStr1); stream.writeDouble(this.testNumber1); } }

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; }
Now, I have the following code to send input to the stored procedure. I've used SQLData to send Java Objects to Sql.

... //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);



Issue: The above code is working fine as long as I send null for the array in IN object to the stored procedure. If I follow the above code structure to send the Java array to SQL's stored procedure I'm facing the following exception when it is trying to create the ARRAY object in getOracleArray().

java.lang.ClassCastException: oracle.sql.ArrayDescriptor cannot be cast to oracle.sql.StructDescriptor at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:195) at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:169) at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:138) at oracle.sql.STRUCT.toSTRUCT(STRUCT.java:493) at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTypeADT.java:328) at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(OracleTypeADT.java:372) at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(OracleTypeUPT.java:110) at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1238) at oracle.sql.ARRAY.<init>(ARRAY.java:103) at TestDataINObjConverter.getOracleArray(TestDataINObjConverter.java:51)

Question is: Can someone help me figure out how I can pass Java array as SQLArray to a object which is in turn passed as a parameter to stored procedure(SP)? If I am not successful with finding the solution for this, I may have to ask the DBA to change the structure of the SP like (object with only basic datatypes, inputarray, output). But I'm assuming there is a better and elegant way to handle this in Java. I appreciate any help or suggestions.


Note: There is no compilation errors in the above code, if I have missed something trivial please ignore it as my effort to make the question small.
Thanks
SK