Re: [eclipselink-users] Using PLSQLStoredProcedureCall

[James Sutherland <jamesssss@xxxxxxxxx>]

No, you want to use a StoredProcedureCall, you have no complex types, and use
a DataReadQuery not ValueReadQuery as you are selecting multiple values, not
a single value.

>>
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("MYPKG.GETEMAILS");
call.addNamedArgument("EMPLOYEEID");
call.useNamedCursorOutputAsResultSet("MYCSR");

DataReadQuery query = new DataReadQuery();
query.setCall(call);
query.addArgument("EMPLOYEEID");

List args = new ArrayList();
args.add(employeeId);

List<DatabaseRecord> results =
(List<DatabaseRecord>)session.executeQuery(query, args);


Oggie wrote:
> 
> Well, I understood a Cursor as a complex PLSQL type. This Cursor
> retrieves me at least one row with two fields and on the worst case two
> rows with the same two fields in every row.
> 
> On the other hand I have not a class Email. It is just a table from the
> database.
> 
> What I am pretending is to get the emails (type and address) to display
> them
> in a JSF page.
> 
> This is the code snippet I have tried,
> 
> PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
> call.setProcedureName("MYPKG.GETEMAILS");
> call.addNamedArgument("EMPLOYEEID", JDBCTypes.NUMERIC_TYPE);
> call.useNamedCursorOutputAsResultSet("MYCSR");
> 
> ValueReadQuery query = new ValueReadQuery();
> query.setCall(call);
> query.addArgument("EMPLOYEEID");
> 
> List args = new ArrayList();
> args.add(employeeId);
> 
> return (List)s.executeQuery(query, args);
> 
> 
> and I get the exception as follows,
> 
> Exception [EclipseLink-6148] (Eclipse Persistence Services - 
> 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.QueryException
> Exception Description: Adding named OUT cursor arguments without 
> DatabaseType classification to PLSQLStoredProcedureCall is not supported.
>                 at 
> org.eclipse.persistence.exceptions.QueryException.addArgumentsNotSupported(QueryException.java:1367)
> 	at 
> org.eclipse.persistence.platform.database.oracle.plsql.PLSQLStoredProcedureCall.useNamedCursorOutputAsResultSet(PLSQLStoredProcedureCall.java:342)
> 	at 
> com.ejb.EmployeeOracleStoredProcedure.findAllEmailAddress(EmployeeOracleStoredProcedure.java:52)
> 	at 
> com.web.EmployeeController.getAllEmailAddress(EmployeeController.java:167)
> etc...
> 
> What do you suggest?
> 
> Thanks,
> Jose
> 
> --------------------------------------------------
> From: "James Sutherland" <jamesssss@xxxxxxxxx>
> Sent: Monday, April 12, 2010 4:36 PM
> To: <eclipselink-users@xxxxxxxxxxx>
> Subject: Re: [eclipselink-users] Using PLSQLStoredProcedureCall
> 
>>
>> What error are you getting?
>>
>> In general your procedure just seems to be using regular database types, 
>> so
>> you should be able to use just a StoredProcedureCall not a
>> PLSQLStoredProcedureCall (which is only required for complex PLSQL types
>> such as RECORD or TABLE).
>>
>> The query code should only be adding the employeeId argument, not the 
>> myCsr
>> (only the input arguments).  Also you need to set the reference class for
>> the query (Email).
>>
>>
>> Oggie wrote:
>>>
>>> Hi,
>>>
>>> I am newbe calling Stored Procedures.
>>>
>>> I am having problems setting a PLSQLStoredProcedureCall() up.
>>> This PLSQL SP call have two arguments, an IN argument EMPLOYEEID,
>>> and an OUT one type of REF CURSOR.
>>> But I do not know how to pass the OUT one argument.
>>>
>>> This is the Oracle SP,
>>> CREATE OR REPLACE package mypkg as
>>>    TYPE csr IS REF CURSOR;
>>>    --
>>>    procedure getEmails(employeeId in number, myCsr out csr);
>>> end;
>>> /
>>> CREATE OR REPLACE package body mypkg as
>>>    procedure getEmails(employeeId in number, myCsr out csr) is
>>>    begin
>>>       open myCsr for
>>>          select email_type, email_address
>>>    from email
>>>    where emp_id = employeeId;
>>>    end getEmails;
>>> end;
>>> /
>>>
>>>
>>> This is my code snippet,
>>>
>>> public List<String[]> findAllEmailAddress(BigDecimal employeeId) {
>>>
>>> // --Initialisation code
>>>
>>> PLSQLStoredProcedureCall call = new PLSQLStoredProcedureCall();
>>> call.setProcedureName("MYPKG.GETEMAILS");
>>> call.addNamedArgument("EMPLOYEEID", JDBCTypes.NUMERIC_TYPE);
>>>
>>> // Here is where I am getting wrong
>>> // I think I have to build PLSQLargument() before passing to
>>> // call.useNamedCursorOutputAsResultSet("MYCSR", dt);
>>> // but I do not know how to do it
>>> // ---------------------------
>>> PLSQLargument pa = new PLSQLargument();
>>> DatabaseType dt = pa.databaseType;
>>> // ---------------------------
>>>
>>> call.useNamedCursorOutputAsResultSet("MYCSR", dt);
>>> ReadAllQuery query = new ReadAllQuery();
>>> query.addArgument("EMPLOYEEID");
>>> query.addArgument("MYCSR");
>>> query.setCall(call);
>>> List queryArgs = new ArrayList();
>>> queryArgs.add(employeeId);
>>> returnList = (List<String[]>)s.executeQuery(query, queryArgs);
>>>  return returnList;
>>> }
>>>
>>> Anny suggestion about this code will be appreciated.
>>>
>>> Thanks in advanced,
>>> Jose
> 
> 


-----
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://old.nabble.com/Using-PLSQLStoredProcedureCall-tp28201039p28287877.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.