How to call stored procedure that returns a cursor
Jitesh Sinha
Ranch Hand
Joined: Jun 19, 2004
Posts: 138
posted
0
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
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!
This message was edited 2 times. Last update was at by Bear Bibeault
This message was edited 1 time. Last update was at by Ireneusz Kordal
Jitesh Sinha
Ranch Hand
Joined: Jun 19, 2004
Posts: 138
posted
0
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?
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.
This message was edited 1 time. Last update was at by Tina Smith
Everything is theoretically impossible, until it is done. ~Robert A. Heinlein
Ireneusz Kordal
Ranch Hand
Joined: Jun 21, 2008
Posts: 415
posted
0
try:
This message was edited 4 times. Last update was at by Ireneusz Kordal
subject: How to call stored procedure that returns a cursor