Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Using PLSQLStoredProcedureCall 
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-tp28201039p28218619.html 
Sent from the EclipseLink - Users mailing list archive at Nabble.com.

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top