chandubcs
asked on
Error in getting Oracle CURSOR from Oracle procedure using REF CURSOR in Websphere WSAD 5
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.getConnectio n(database URL, 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 code used to work perfectly in weblogic 5. Now, I am using Websphere Studio WSAD 5, Oracle 8.1.7, Oracle thin driver.
One more thing here is... when I change the 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 result with no errors. What could be the problem? Is that the oracle.jdbc.driver.OracleT ypes.CURSO R ???
Could anyone please advice me.
Thanks,
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.getConnectio
cs = conn.prepareCall("{call FIRST(?)}");
cs.registerOutParameter(1,
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 code used to work perfectly in weblogic 5. Now, I am using Websphere Studio WSAD 5, Oracle 8.1.7, Oracle thin driver.
One more thing here is... when I change the OUT parameter in Oracle to VARCHAR2 and assign some value to it and in JSP if I use cs.registerOutParameter(1,
Could anyone please advice me.
Thanks,
ASKER
Hi,
I got the solution by myself. In oracle, TYPE ref_cursor IS REF CURSOR will work only with the Oracle packages. So, when I kept my oracle procedures into a package, it started working. Thanks for your comment though.
I got the solution by myself. In oracle, TYPE ref_cursor IS REF CURSOR will work only with the Oracle packages. So, when I kept my oracle procedures into a package, it started working. Thanks for your comment though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oracle.jdbc.OracleTypes.CU