[
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.
|
Include the code you are now using for the procedure call.
SaikatSen wrote:
>
> 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.
>> >>
>> >>
>> >
>> >
>>
>
>
-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.eclipse.org/forums/index.php?t=thread&frm_id=111&S=1b00bfd151289b297688823a00683aca
EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
Blog: http://java-persistence-performance.blogspot.com/ Java Persistence
Performance
--
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.-tp33149957p33243403.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
