[eclipselink-users] Error while calling a PLSQL stored procedure having

[SaikatSen <charmingsaikat@xxxxxxxxx>]

These are the types which I have:

TYPE LR_QUERY_TABLE AS TABLE OF LR_QUERY_TYPE;

TYPE LR_QUERY_TYPE AS OBJECT(queryType VARCHAR2(64),queryString
varchar2(16000));

This is my stored procedure:

create or replace
PROCEDURE MY_TEST_PROCEDURE
(wbdatareqd IN lr_query_table,
 pqr IN VARCHAR2,
result OUT VARCHAR2)
AS abc VARCHAR2(4000);
BEGIN
 SELECT lrqt.queryString INTO abc  FROM TABLE(wbdatareqd) lrqt WHERE
lrqt.queryType = 'Itinerary';
result :=abc;

EXCEPTION
WHEN OTHERS THEN
result := 'error***' || sqlerrm;
end  MY_TEST_PROCEDURE;

These are the classes which I have:

@Embeddable
@Struct(name="LR_QUERY_TYPE", fields={"queryType","queryString"})
public class MyType extends ComplexDatabaseType {

	@Column(name="queryType")
	private String queryType;
	
	@Column(name="queryString")
	private String queryString;

	public String getQueryType() {
		return queryType;
	}

	public void setQueryType(String queryType) {
		this.queryType = queryType;
	}

	public String getQueryStr() {
		return queryString;
	}

	public void setQueryStr(String queryStr) {
		this.queryString = queryStr;
	}



@PLSQLTable(compatibleType="LR_QUERY_TABLE",name="LR_QUERY_TABLE",nestedType="LR_QUERY_TYPE",javaType=MyTableTyp.class)
public class MyTableTyp extends ArrayList {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
}

This is the code I am trying to run:
	 
		PLSQLStoredProcedureCall plsqlcall = new PLSQLStoredProcedureCall();
		plsqlcall.setProcedureName("MY_TEST_PROCEDURE");
		
		MyType myType=new MyType();
		myType.setCompatibleType("LR_QUERY_TYPE");
		myType.setJavaType(MyType.class);
		myType.setTypeName("LR_QUERY_TYPE");
		
		PLSQLCollection plSqlCollection=new PLSQLCollection();
		plSqlCollection.setCompatibleType("LR_QUERY_TABLE");
		plSqlCollection.setJavaType(MyTableTyp.class);
		plSqlCollection.setNestedType(myType);
		plSqlCollection.setTypeName("LR_QUERY_TABLE");
		
		plsqlcall.addNamedArgument("wbdatareqd",plSqlCollection);
		plsqlcall.addNamedArgument("pqr",JDBCTypes.VARCHAR_TYPE);
		plsqlcall.addNamedOutputArgument("result",JDBCTypes.VARCHAR_TYPE);	
		
		DataReadQuery  readQuery=new DataReadQuery();
		readQuery.addArgument("wbdatareqd",MyTableTyp.class);
		readQuery.addArgument("pqr",String.class);
        readQuery.setCall(plsqlcall);
        readQuery.bindAllParameters();
        
        Vector args=new Vector();
      
        MyTableTyp myTableType=new MyTableTyp(); 
        myType.setQueryStr("ITN_TYP_CD FROM ITN WHERE ITN_ID=22");
        myType.setQueryType("Itinerary");
        myTableType.add(myType);
        args.add(myTableType);
        args.add("test");
       
       Object result=
jpaEntityManager.getActiveSession().executeQuery(readQuery,args);



This is the error that I get :

Caused by: java.sql.SQLException: Fail to convert to internal
representation: MyType(LR_QUERY_TYPE)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
	at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
	at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTypeADT.java:259)
	at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(OracleTypeADT.java:303)
	at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(OracleTypeUPT.java:117)
	at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1517)
	at oracle.sql.ARRAY.<init>(ARRAY.java:133)
	at
org.eclipse.persistence.platform.database.oracle.Oracle8Platform.createArray(Oracle8Platform.java:267)
	at
org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.createArray(DatabasePlatform.java:2923)
	at
org.eclipse.persistence.internal.databaseaccess.BindCallCustomParameter.convert(BindCallCustomParameter.java:142)
	at
org.eclipse.persistence.internal.databaseaccess.InParameterForCallableStatement.set(InParameterForCallableStatement.java:30)
	at
org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:2229)
	at
org.eclipse.persistence.platform.database.oracle.Oracle9Platform.setParameterValueInDatabaseCall(Oracle9Platform.java:476)
	at
org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:716)
	at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:585)
	... 18 more.


Any idea what changes  I need to do in the code to make this working. I have
been working on this for the past 2 days without any result. Thanks for any
help in advance

Saikat.

-- 
View this message in context: http://old.nabble.com/Error-while-calling-a-PLSQL-stored-procedure-having-a-PLSQLTable-type-as-one-of-its-IN-parameter.-tp33149957p33149957.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.