Hi,
Till this morning I was sure that Oracle CURSOR types where fully
supported in Cayenne (they are tested in our unit test suite after
all). Now checking the relevant code, I must admit that the Modeler
and mapping loader would not allow DB-specific types. Here is a
workaround you can try. In your app somewhere on startup you can
reset the type of the parameter to the oracle CURSOR:
Procedure p = ....;
Iterator it = p.getCallOutParameters().iterator();
while(it.hasNext()) {
ProcedureParameter parameter = (ProcedureParameter) it.next();
if("V_RESULT_CURSOR".equals(parameter.getName())) {
parameter.setType(OracleTypes.CURSOR);
break;
}
}
Let us know if that worked. Also appreciate opening a Jira
improvement request.
Thanks
Andrus
On May 15, 2007, at 4:38 PM, Marc Gabriel-Willem wrote:
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
