cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <>
Subject Re: Stored procedures - more examples?
Date Tue, 18 Jan 2011 20:33:25 GMT

On Jan 18, 2011, at 8:28 PM, David BalaĹžic wrote:

> So, does that testcase really work on Oracle?

Yes. Now revisiting the tests, we are cheating to make the generic mapping work with Oracle.
The reason for this is Oracle driver's insistence on circumventing basic JDBC abstractions.
We take a generic mapping [1] and alter the procedure object in the test code [2]. Notice
how we install Oracle-specific type as an OUT parameter type:

// the type used below is "oracle.jdbc.OracleTypes.CURSOR"
proc.addCallParameter(new ProcedureParameter("result", OracleAdapter.getOracleCursorType(),

For now the above (creating or tweaking procedure mapping metadata in the code) is one possible
way for dealing with Oracle. Going forward, we may either support numeric values for column
types (so that a user could map this column as "-10", same as "oracle.jdbc.driver.OracleTypes.CURSOR").
Or maybe we should dynamically rewrite Cayenne-mapped stored procedures, adding CURSOR parameter
inside Cayenne (not yet sure how what part of the mapping should trigger such behavior).\

And unfortunately there's little hope that after all these years Oracle will finally fix their
driver (or at least implement ParameterMetaData JDBC interface so that Oracle-specific types
could be used dynamically).



<procedure name="cayenne_tst_select_proc">
  <procedure-parameter name="aName" type="VARCHAR" length="254" direction="in"/>
  <procedure-parameter name="paintingPrice" type="INTEGER" direction="in"/>


public void tweakProcedure(Procedure proc) {
       if (DataContextProcedureQueryTest.SELECT_STORED_PROCEDURE.equals(proc.getName())
               && proc.getCallParameters().size() == 2) {
           List params = new ArrayList(proc.getCallParameters());

           proc.addCallParameter(new ProcedureParameter("result", OracleAdapter
                   .getOracleCursorType(), ProcedureParameter.OUT_PARAMETER));
           Iterator it = params.iterator();
           while (it.hasNext()) {
               ProcedureParameter param = (ProcedureParameter);


View raw message