[eclipselink-users] Using PLSQLStoredProcedureCall

["Jose Alvarez de Lara" <dakhla.0563@xxxxxxxxxxx>]

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