Stored Procedure and Java

[Su Chikki]

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

[Wendy L Gibbons]

I can't offer any actual advise, but your error message returned this link, which looked good:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:712625135727

[Su 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.