Win a copy of Rust Web Development this week in the Other Languages forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

How to call stored procedure that returns a cursor

 
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
can someone please respond to this?Thanks.
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 208
9
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

try:

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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/
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic