cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Oracle stored procedure - registering out ref cursor
Date Wed, 16 May 2007 05:58:07 GMT
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
>


Mime
View raw message