| Author |
How to call stored procedure that returns a cursor
|
Jitesh Sinha
Ranch Hand
Joined: Jun 19, 2004
Posts: 139
|
|
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: 139
|
|
|
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: 139
|
|
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: 112
|
|
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:
|
 |
 |
|
|
subject: How to call stored procedure that returns a cursor
|
|
|