cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marc Gabriel-Willem" <marc.gabriel-wil...@side-international.com>
Subject Oracle stored procedure - registering out ref cursor
Date Tue, 15 May 2007 13:38:13 GMT
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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message