Hi!
Thanks for the info. I tried "cayenne_tst_out_proc" and
"cayenne_tst_select_proc".
The first works, but the second fails (with Oracle 10g).
This is what I get:
INFO: {call cayenne_tst_select_proc(?, ?)} [bind: 1:'An Artist', 2:3000]
18.1.2011 19:15:02 org.apache.cayenne.access.QueryLogger logQueryError
INFO: *** error.
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'CAYENNE_TST_SELECT_PROC' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I think the problem is that for Oracle a function must be called like this:
{? = call cayenne_tst_select_proc(?, ?)}
If I change the mapping to :
<procedure name="cayenne_tst_select_proc" returningValue="true">
<procedure-parameter name="OUT" type="OTHER" direction="out"/>
<procedure-parameter name="aName" type="VARCHAR" length="254"
direction="in"/>
<procedure-parameter name="paintingPrice" type="INTEGER"
direction="in"/>
</procedure>
Then I get the correct call, but the type gives an error:
INFO: {? = call cayenne_tst_select_proc(?, ?)} [bind: 1:'[OUT]', 2:'An Artist',
3:3000]
18.1.2011 19:23:44 org.apache.cayenne.access.QueryLogger logQueryError
INFO: *** error.
java.sql.SQLException: Invalid column type
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:209)
at
oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3462)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:126)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:285)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:371)
at
org.apache.cayenne.access.trans.ProcedureTranslator.setOutParam(ProcedureTranslator.java:228)
at
org.apache.cayenne.access.trans.ProcedureTranslator.initStatement(ProcedureTranslator.java:176)
at
org.apache.cayenne.access.trans.ProcedureTranslator.createStatement(ProcedureTranslator.java:150)
at
org.apache.cayenne.access.jdbc.ProcedureAction.performAction(ProcedureAction.java:70)
at
org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:269)
at
org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:422)
at
org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:69)
at
org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:395)
at
org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:850)
at
org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:392)
at
org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:743)
at
org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:333)
at
org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96)
at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1278)
at
org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1267)
at test.stein.Dummy1.callSP1(Dummy1.java:54)
So, does that testcase really work on Oracle?
If yes, what am I doing wrong?
Regards,
David
> -----Original Message-----
> From: Andrus Adamchik [mailto:[email protected]]
> Sent: Tuesday, January 18, 2011 2:12 PM
> To: [email protected]
> Subject: Re: Stored procedures - more examples?
> Importance: Low
>
>
>
> On Jan 17, 2011, at 7:27 PM, David Balažic wrote:
>
> > Hi!
> >
> > Are there any more examples of using stored procedures
> besides the ones
> > in the guide*?
>
> I can't think of any except for unit tests in Cayenne under
> DataContextProcedureQueryTest.java. Others may have better examples.
>
> > What exactly is the meaning of the returningValue attribute?
> > Is it needed if any parameter is of type OUT?
> > Or when the procedure is actually a function?
>
> Yes. This is for functions support (see example below). Not
> all DB's support this. IIRC this was introduced for Oracle
> and PostgreSQL.
>
> > How is the result set (cursor) support for Oracle?
> > In the Modeller there is no CURSOR type, should OTHER be used?
>
> Here is an Oracle example. To access ResultSet in Cayenne,
> "Return Value" checkbox must be checked:
>
> CREATE OR REPLACE PACKAGE cayenne_types
> AS
> TYPE ref_cursor IS REF CURSOR;
> END;
>
>
> CREATE OR REPLACE FUNCTION cayenne_tst_select_proc (a_name IN
> VARCHAR2, painting_price IN NUMBER)
> RETURN cayenne_types.ref_cursor
> AS
> artists cayenne_types.ref_cursor;
> BEGIN
> SET TRANSACTION READ WRITE;
> UPDATE PAINTING SET ESTIMATED_PRICE = ESTIMATED_PRICE * 2
> WHERE ESTIMATED_PRICE < painting_price;
> COMMIT;
>
> OPEN artists FOR
> SELECT DISTINCT A.ARTIST_ID, A.ARTIST_NAME, A.DATE_OF_BIRTH
> FROM ARTIST A, PAINTING P
> WHERE A.ARTIST_ID = P.ARTIST_ID AND
> RTRIM(A.ARTIST_NAME) = a_name
> ORDER BY A.ARTIST_ID;
>
> RETURN artists;
> END;
>
> The same example in MySQL. MySQL returns ResultSet without a
> need for return value mapping:
>
> CREATE PROCEDURE cayenne_tst_select_proc (IN p1 varchar(200),
> IN p2 DECIMAL)
> BEGIN
> UPDATE PAINTING SET ESTIMATED_PRICE = ESTIMATED_PRICE * 2
> WHERE ESTIMATED_PRICE < p2;
>
> SELECT DISTINCT A.ARTIST_ID, A.ARTIST_NAME, A.DATE_OF_BIRTH
> FROM ARTIST A, PAINTING P
> WHERE A.ARTIST_ID = P.ARTIST_ID AND
> RTRIM(A.ARTIST_NAME) = p1
> ORDER BY A.ARTIST_ID;
> END
>
> While the mapping would differ between the DB's, Java code is
> the same for both Oracle and MySQL:
>
> ProcedureQuery q = new ProcedureQuery("cayenne_tst_select_proc");
> q.addParameter("aName", "An Artist");
> q.addParameter("paintingPrice", new Integer(3000));
> List artists = ctxt.performQuery(q);
>
>
> Andrus
>
