Re: [eclipselink-users] Using PLSQLStoredProcedureCall

[James Sutherland <jamesssss@xxxxxxxxx>]

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-tp28201039p28218619.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.