[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Error while calling a PLSQL stored procedure having a PLSQLTable type as one of its IN parameter.
|
The struct type finally worked for me in eclipse link. Thanks to James and
Christopher.
This is the code that I used:
ObjectRelationalDataTypeDescriptor descriptor = new
ObjectRelationalDataTypeDescriptor();
descriptor.setJavaClass(MyType.class);
descriptor.descriptorIsAggregate();
descriptor.setStructureName("LR_QUERY_TYPE");
descriptor.setTableName("LR_QUERY_TABLE");
descriptor.addDirectMapping("queryType", "getQueryType",
"setQueryType", "queryType".toUpperCase());
descriptor.addDirectMapping("queryString", "getQueryString",
"setQueryString", "queryString".toUpperCase());
descriptor.setShouldOrderMappings(false);
sess.addDescriptor(descriptor);
plsqlcall.addNamedArgument("wbdatareqd","wbdatareqd",JDBCTypes.ARRAY_TYPE.getSqlCode(),"LR_QUERY_TABLE");
List parameters=plsqlcall.getParameters();
for(int i=0; i<parameters.size(); i++)
{
DatabaseField param= (DatabaseField)parameters.get(i);
System.out.println(param.getClass().getName());
if("wbdatareqd".equals(param.getName()))
{
ObjectRelationalDatabaseField ordf = new
ObjectRelationalDatabaseField("");
ordf.setSqlType(Types.STRUCT);
ordf.setSqlTypeName("LR_QUERY_TYPE");
ordf.setType(MyType.class);
((ObjectRelationalDatabaseField)(parameters.get(i))).setSqlType(Types.ARRAY);
((ObjectRelationalDatabaseField)(parameters.get(i))).setSqlTypeName("LR_QUERY_TABLE");
((ObjectRelationalDatabaseField)(parameters.get(i))).setType(ArrayList.class);
((ObjectRelationalDatabaseField)(parameters.get(i))).setNestedTypeField(ordf);
}
}
Regards
Saikat.
SaikatSen wrote:
>
> 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.-tp33149957p33190521.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
