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
>
This archive was generated by hypermail 2.0.0 : Wed May 16 2007 - 01:58:48 EDT