Dear all,
We are trying to map the following oracle stored procedure using the
cayenne modeler.
Please find below all the details of the PL/SQL required to create the
stuff.
<<<< --- >>>>
create table tTest
(
id int NOT NULL,
data varchar2(200) NOT NULL,
lastupdate date NOT NULL,
description varchar2(200) NULL
);
CREATE OR REPLACE PACKAGE TestPackage
AS
-- Record Type definition
TYPE TestRecord IS RECORD
(
id int ,
data varchar2(200),
lastupdate date,
description varchar2(200)
);
-- Ref cursor definition
TYPE TestCursorRef IS REF CURSOR RETURN TestRecord;
END TestPackage;
-- stored proc fetching data
create or replace PROCEDURE spTestGet( v_result_cursor IN OUT
TestPackage.TestCursorRef )
as
BEGIN
OPEN v_result_cursor FOR SELECT id, data, lastupdate,
description from tTest;
END ;
<<<< --- >>>>
Using JDBC, we are able to use that stored procedure using the following
instructions:
...
CallableStatement statement = db.prepareCall ("{ call spTestGet(?) }");
statement.registerOutParameter(1, OracleTypes.CURSOR);
statement.executeUpdate();
ResultSet rs = ((OracleCallableStatement)statement).getCursor(1);
while (rs.next())
{
...
}
...
Using the "reengineer database" modeler function, the following has been
automatically mapped:
<procedure name="SPTESTGET" schema="DDA">
<procedure-parameter name="V_RESULT_CURSOR" type="OTHER"
direction="in_out"/>
</procedure>
Unfortunately, cayenne throws the following error:
INFO QueryLogger: Detected and installed adapter:
org.apache.cayenne.dba.oracle.OracleAdapter
INFO QueryLogger: {call DDA.SPTESTGET(?)} [bind: NULL]
INFO QueryLogger: *** error.
java.sql.SQLException: Invalid column type
at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at
oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.jav
a:6164)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameterBytes(Ora
cleCallableStatement.java:244)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCa
llableStatement.java:393)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCa
llableStatement.java:462)
at
org.apache.cayenne.access.trans.ProcedureTranslator.setOutParam(Procedur
eTranslator.java:217)
...
We think the error provides from the fact that the parameter type is
probably incorrect. The modeler sets the type to "OTHER". As you could
see, using a standard JDBC call the type is set to "OracleTypes.CURSOR".
Is there a way to specify that "CURSOR" type too?
Thank you for your help.
Marc Gabriel
This archive was generated by hypermail 2.0.0 : Tue May 15 2007 - 09:38:57 EDT