Re: Oracle stored procedure - registering out ref cursor

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Wed May 16 2007 - 01:58:07 EDT

  • Next message: Andrus Adamchik: "[ANN] Apache Cayenne 1.2.3 and 2.0.3 release"

    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