aspose file tools*
The moose likes JDBC and the fly likes Cannot get Oracle CURSOR from Oracle procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Cannot get Oracle CURSOR from Oracle procedure" Watch "Cannot get Oracle CURSOR from Oracle procedure" New topic
Author

Cannot get Oracle CURSOR from Oracle procedure

chandubcs
Ranch Hand

Joined: Jun 03, 2001
Posts: 47
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

Joined: Jun 03, 2001
Posts: 47
Anyone please advice me about my JDBC performance issue...its urgent...thanks
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

cut and pasted from the Oracle JDBC documentation ( here ) with your PL/SQL call:

Jamie
chandubcs
Ranch Hand

Joined: Jun 03, 2001
Posts: 47
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,
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Cannot get Oracle CURSOR from Oracle procedure