This week's book giveaway is in the Cloud forum.
We're giving away four copies of Terraform in Action and have Scott Winkler on-line!
See this thread for details.
Win a copy of Terraform in Action this week in the Cloud 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
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Cannot get Oracle CURSOR from Oracle procedure

 
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
I need to retrieve a OracleTypes.CURSOR in the form of java ResulstSet into my JSP page from Oracle database.
The JSP looks like:
<%@ page import = "java.sql.*" %>
<%@ page import = "oracle.jdbc.driver.*" %>
......
conn = DriverManager.getConnection(databaseURL, databaseUsername, databasePassword);
cs = conn.prepareCall("{call FIRST(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
rs = (ResultSet) cs.getObject(1);
...........
Oracle procedure is:
.............
TYPE ref_cursor IS REF CURSOR;
CREATE OR REPLACE PROCEDURE FIRST (
r_cursor OUT ref_cursor
) IS
BEGIN
open r_cursor for
select a, b, c from table;
END;
This code is giving me an error as "java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'FIRST' ORA-06550: line 1, column 7: PL/SQL: Statement ignored ", but the same works perfect in weblogic 5. Now, I am using WSAD 5, Oracle 8.1.7.
One more thing here is... when I change OUT parameter in Oracle to VARCHAR2 and assign some value to it and in JSP if I use cs.registerOutParameter(1, OracleTypes.VARCHAR), it is working fine and giving me the proper result. I guess the problem is with oracle.jdbc.driver.OracleTypes.CURSOR???
Could anyone please advice me.
Thanks,
 
chandubcs
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Anyone please advice me about my JDBC performance issue...its urgent...thanks
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
cut and pasted from the Oracle JDBC documentation ( here ) with your PL/SQL call:

Jamie
 
chandubcs
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I got the solution. The Oracle cursor declaration "TYPE ref_cursor IS REF CURSOR" has to be in a Oracle package. So, I am using Oracle package in procedures now and calling that package.procedure from Java using JDBC.
Thanks,
reply
    Bookmark Topic Watch Topic
  • New Topic