This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to call stored procedure that returns a cursor

 
Jitesh Sinha
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 146
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can someone please respond to this?Thanks.
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 146
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 208
9
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

try:

 
Victor Lindberg
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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/
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic