Home » Eclipse Projects » EclipseLink » Error when trying to use PLSQLTable type as an IN parameter in a PLSQLStoredProcedure in eclipse lin
Error when trying to use PLSQLTable type as an IN parameter in a PLSQLStoredProcedure in eclipse lin [message #779936] |
Mon, 16 January 2012 21:12 |
Saikat Sen Messages: 2 Registered: January 2012 |
Junior Member |
|
|
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.
|
|
|
Re: Error when trying to use PLSQLTable type as an IN parameter in a PLSQLStoredProcedure in eclipse [message #780855 is a reply to message #779936] |
Thu, 19 January 2012 16:36 |
|
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.
James : Wiki : Book : Blog : Twitter
|
|
|
Re: Error when trying to use PLSQLTable type as an IN parameter in a PLSQLStoredProcedure in eclipse [message #781006 is a reply to message #779936] |
Thu, 19 January 2012 16:36 |
|
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.
--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
James : Wiki : Book : Blog : Twitter
|
|
|
Re: Error when trying to use PLSQLTable type as an IN parameter in a PLSQLStoredProcedure in eclipse [message #781023 is a reply to message #779936] |
Thu, 19 January 2012 16:36 |
|
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.
--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
James : Wiki : Book : Blog : Twitter
|
|
|
Re: Error when trying to use PLSQLTable type as an IN parameter in a PLSQLStoredProcedure in eclipse [message #781040 is a reply to message #779936] |
Thu, 19 January 2012 16:36 |
|
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.
--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
James : Wiki : Book : Blog : Twitter
|
|
|
Goto Forum:
Current Time: Sat Nov 02 23:36:46 GMT 2024
Powered by FUDForum. Page generated in 0.03348 seconds
|