Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: AW: Re: [eclipselink-users] StoredFunctionCall with ROWTYPE as returnvalue 
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

James Sutherland schrieb:

The PLSQL support was only added to for stored procedures.  For a stored
function you will need to wrap it in a stored procedure, or customize your
own subclass of PLSQLStoredProcedureCall.


Martin.Berner wrote:

Hello,
I replaced the following jar's in glassfish-v3-prelude/glassfish/modules/ with the ones shipped with "eclipselink-plugins-1.1.0.r3634.zip": 
org.eclipse.persistence.core_1.1.0.r3634.jar
org.eclipse.persistence.asm_1.1.0.r3634.jar
org.eclipse.persistence.antlr_1.1.0.r3634.jar
org.eclipse.persistence.oracle_1.1.0.r3634.jar
javax.persistence_1.99.0.jar
org.eclipse.persistence.jpa_1.1.0.r3634.jar
When Eclipselink connect to the Database at least it shows me (Eclipse Persistence Services - 1.1.0.r3634). 

I don't know if tat dos the trick put it works at least.
Any other Idees?
About the Problem I'm asking initially, I don't know how to use PLSQLStoredProcedureCall with a Function returning a row. I found a sample at (http://forums.oracle.com/forums/thread.jspa?threadID=829153) on which a User explains how to use PLSQLStoredProcedureCall with a RowType as IN, OUT or INOUT Parameter. But I hope I don't have to code a wrapper-Procedure for every single Function. Can Anybody give me a Sample for accessing a storedFunction like this one? 

FUNCTION rowGetZwParameter(pnParID IN NUMBER) RETURN
T_ZWPARAMETER%ROWTYPE;






-----
---
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

Back to the top