[
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.
|
I have a different question now though.
When I use a table which has nested table of type struct as its input type
like say LR_QUERY_TABLE it understands the annotation and inserts value
inside the table.
But for stored procedure if it has the same table type(LR_QUERY_TABLE which
has LR_QUERY_TYPE inside it) as its input parameter why does it not read
the annotations.
Do we always have to use ObjectRelationalDataTypeDescriptor for it to make
it understand the input type.
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.-tp33149957p33190558.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
