[
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.
|
Hi All,
Any idea about how to pass a STRUCT type object to a PLSQL stored procedure. I have tried annotation @Struct. I have tried using ObjectRelationalDataTypeDescriptor like below but nothing has helped. Any help would be really appreciable.
ObjectRelationalDataTypeDescriptor descriptor = new
ObjectRelationalDataTypeDescriptor();
descriptor.setJavaClass(MyType.class);
descriptor.descriptorIsAggregate();
descriptor.setStructureName("ODSR27.LR_QUERY_TYPE");
descriptor.addFieldOrdering("queryType".toUpperCase());
descriptor.addFieldOrdering("queryString".toUpperCase());
descriptor.addDirectMapping("queryType", "getQueryType",
"setQueryType", "queryType".toUpperCase());
descriptor.addDirectMapping("queryString", "getQuerySt ring",
"setQueryString", "queryString".toUpperCase());
//descriptor.setShouldOrderMappings(false);
sess.addDescriptor(descriptor);
No matter what I try it always tells me:
Fail to convert to internal representation: com.aa.persistence.asbo.MyType@30043004 which is a STRUCT type.
Regards
Saikat
On Thu, Jan 19, 2012 at 8:56 AM, SaikatSen
<charmingsaikat@xxxxxxxxx> wrote:
Hi,
It worked when I used a StoredProcedureCall and an ArrayList in the
argument.
But inside the ArrayList I had to pass a STRUCT object for type
LR_QUERY_TYPE like this:
StructDescriptor itemDescriptor =
StructDescriptor.createDescriptor("ODSR27.LR_QUERY_TYPE", con);
Object[] itemAtributes = new Object[] {new String("abcd"),new
String("anyValue") };
itemObject1 = new STRUCT(itemDescriptor, connection, itemAtributes);
What would be the eclipse link equivalent for STRUCT?
I have tried
ObjectRelationalDataTypeDescriptor descriptor = new
ObjectRelationalDataTypeDescriptor();
descriptor.setJavaClass(MyType.class);
descriptor.descriptorIsAggregate();
descriptor.setStructureName("ODSR27.LR_QUERY_TYPE");
descriptor.addFieldOrdering("queryType".toUpperCase());
descriptor.addFieldOrdering("queryString".toUpperCase());
descriptor.addDirectMapping("queryType", "getQueryType",
"setQueryType", "queryType".toUpperCase());
descriptor.addDirectMapping("queryString", "getQuerySt ring",
"setQueryString", "queryString".toUpperCase());
//descriptor.setShouldOrderMappings(false);
sess.addDescriptor(descriptor);
then
populated the MyType object which contains attribute queryType and
queryString
Added the MyType object inside the ArrayList but it did not work.
Any idea on how to populate STRUCT type object using eclipse link would be
really helpful.
I still get his error:
Error Code: 17059
Call: BEGIN MY_TEST_PROCEDURE(wbdatareqd=>?, pqr=>?, result=>?); END;
bind => [null, test, => result]
Query: DataModifyQuery()
Exception in thread "main" Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services -
2.3.2.v20111125-r10461):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Fail to convert to internal
representation: com.aa.persistence.asbo.MyType@4da04da0
Error Code: 17059
Call: BEGIN MY_TEST_PROCEDURE(wbdatareqd=>?, pqr=>?, result=>?); END;
bind => [null, test, => result]
Query: DataModifyQuery()
James Sutherland wrote:
>
> A few things,
>
> - Normally VARRAY is used not a nested table, for VARRAYs and OBJECT TYPEs
> you do not need to use PLSQLStoredProcedureCall, only the regular
> StoredProcedureCall. PLSQLStoredProcedureCall is only required for PLSQL
> types (TABLE, RECORD, BOOLEAN, etc.)
>
>>> MyTableTyp extends ArrayList
> - Do not create a class for the TABLE type, it is just a collection, a
> List will be used. You can define the PLSQLTable type on the MyType
> class, but because you are not using a PLSQL TABLE type (you are using a
> nested TABLE), you don't need this at all. Just pass the TABLE argument
> as a List.
>
>>> MyType extends ComplexDatabaseType
> - Do not extend ComplexDatabaseType, this is an EclipseLink class, just
> extend Object.
>
> - You should use a StoredProcedureCall, not a PLSQLStoredProcedureCall.
>
>
>
> 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.-tp33149957p33168350.html
