GeeCON Prague 2014*
The moose likes JDBC and the fly likes How to call stored procedure that returns a cursor Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to call stored procedure that returns a cursor" Watch "How to call stored procedure that returns a cursor" New topic
Author

How to call stored procedure that returns a cursor

Jitesh Sinha
Ranch Hand

Joined: Jun 19, 2004
Posts: 146
Guyz,
I have spent several days trying to do this but have not been successful.I need to call an Oracle stored procedure from my java code.
My stored procedure looks like this :-

create or replace
PROCEDURE GET_GROUP_DATA(P_CURSOR OUT sys_refcursor) AS

L_CATEGORY VARCHAR2(250);
L_GOAL NUMBER;
L_MONTH_TO_DATE NUMBER;
L_STATUS VARCHAR2(50);


BEGIN
OPEN P_CURSOR for

SELECT
T1.CATEGORY
, T1.GOAL
, T1.MONTH_TO_DATE
,T1.status
from T1 ;

end get_group_data;


Here is my Java code :-


Here is the error that I get - java.sql.SQLException: ORA-06553: PL**S*(ignore all *)-306: wrong number or types of arguments in call to 'GET_GROUP_DATA'.

The error message does not have * - but Javaranch thinks I am using abbreviation for PLEASE - so I had to put in * in the middle.
Please help me guyz.Thanks!
Jitesh Sinha
Ranch Hand

Joined: Jun 19, 2004
Posts: 146
can someone please respond to this?Thanks.
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Which Oracle version - 7, 8, 9, 10 or 11 ?
This is described in documentation - look for JDBC Developer Guide.
Assuming you are using 11.2, here is a link to documentation with an example how to do it: http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm#i1058743
Jitesh Sinha
Ranch Hand

Joined: Jun 19, 2004
Posts: 146
Oracle version is 9.
Only difference from the example that you mentioned is execute instead of executeQuery on CallableStatement object.
Can that be the issue?
Tina Smith
Ranch Hand

Joined: Jul 21, 2011
Posts: 171
    
    5

I use "{call package_name.procedure_value(?)}". Not sure if the curly braces makes any difference, but the package name might. Also use execute() and class CallableStatement.

Make sure the package that defines the procedure has a public synonym defined: you will get this error if it does not. Bit misleading in my opinion but it's probably done for security reasons.


Everything is theoretically impossible, until it is done. ~Robert A. Heinlein
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423

try:

Victor Lindberg
Greenhorn

Joined: Feb 13, 2012
Posts: 5
Look this framework (https://code.google.com/p/lindbergframework/). Easy handling of stored procedures and functions, including treatment of cursors automatically.

https://code.google.com/p/lindbergframework/
 
GeeCON Prague 2014
 
subject: How to call stored procedure that returns a cursor