You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Marc Gabriel-Willem <ma...@side-international.com> on 2007/05/15 15:38:13 UTC
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
Re: Oracle stored procedure - registering out ref cursor
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
>