Re: AW: Re: [eclipselink-users] StoredFunctionCall with ROWTYPE as retur

[James Sutherland <jamesssss@xxxxxxxxx>]

There are some examples in the EclipseLink SVN repository under testing,

/foundation/eclipselink.core.test/org/eclipse/persistence/testing/models/plsql

Procedures with %ROW_TYPES are complicated things, as even JDBC does not
support returning PLSQL record types.  You may need to map an
object-relational-data-type descriptor for the row, and create a
corresponding object-type on the database to wrap the row-type.

You may also wish to try to use raw JDBC to access the function, or use a
PLSQL block that flattens the row-type into multiple basic output values, or
wrap the function in a procedure that does such.



Martin.Berner wrote:
> 
> Hello James,
> I tried to access a stored procedure with PLSQLStoredPrecedureCall with 
> no success:
> Procedure and Function are:
>   PROCEDURE getZwParameter(pnParID IN NUMBER, rowZwPar OUT 
> T_ZWPARAMETER%ROWTYPE) IS
>   Begin
>     rowZwPar := rowGetZwParameter(pnParID);
>   end;
> 
>   FUNCTION rowGetZwParameter(pnParID IN NUMBER) RETURN 
> T_ZWPARAMETER%ROWTYPE IS
>     rowRetPar T_ZWPARAMETER%ROWTYPE;
>   BEGIN
>     FOR rowPar IN ( SELECT *
>                       FROM T_ZWPARAMETER
>                      WHERE ZWPAR_ID = pnParID) LOOP
>       rowRetPar := rowPar;
>     END LOOP;
> 
>     RETURN rowRetPar;
>   END;
> 
> Java code snipped are:
>         PLSQLrecord aRecord = new PLSQLrecord();
> //        aRecord.setHasCompatibleType(true);
>         aRecord.setRecordName("rowZwPar");
>         aRecord.setTypeName("T_Zwparameter%ROWTYPE");
> //        aRecord.setCompatibleType("BMPROD_TYPE");
> //        aRecord.addField("ZWPAR_ID", JDBCTypes.NUMERIC_TYPE);
> //        aRecord.addField("ZWPAR_BASISNAME", JDBCTypes.VARCHAR_TYPE, 40);
> //
>         PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
>         call.setProcedureName("PA_ZWS.getZwParameter");
>         call.addNamedArgument("pnParID", JDBCTypes.NUMERIC_TYPE);
>         call.addNamedOutputArgument("rowZwPar", aRecord);
> 
> 
>         ReadObjectQuery roq = new ReadObjectQuery(TZwparameter.class);
>         roq.addArgument("rowZwPar");
>         roq.setCall(call);
> 
>         List<Integer> queryArgs = new ArrayList<Integer>();
>         queryArgs.add(Integer.valueOf(22));
>         TZwparameter zwpar = 
> (TZwparameter)ClientUtility.getClientManager().getDataManager().getSession().executeQuery(roq, 
> queryArgs);
> 
> It doesn't work!  With  Eclipselink 1.1.0 there are fewer methods in 
> Class PLSQLrecord which doesn't allow me to set  setTypeName for example.
> Is it possible that the API has changed?
> 
> When I execute the code wit Eclipselink 1.0.2 then I'm running in a 
> Nullpointerexception.
> 
> Can you please give me a working Sample of accessing a stored procedure 
> with a parameter of "%ROWTYPE"?
> 
> Best regards, Berner Martin
> 
> 


-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/StoredFunctionCall-with-ROWTYPE-as-returnvalue-tp23368567p23407587.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.