Oracle stored procedure - registering out ref cursor

From: Marc Gabriel-Willem (marc.gabriel-wille..ide-international.com)
Date: Tue May 15 2007 - 09:38:13 EDT

  • Next message: Andrus Adamchik: "Re: Oracle stored procedure - registering out ref cursor"

    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