Oracle Procedure Out parameter as Cursor

[satyaki roy]

Hi All ,

I have a number of Oracle stored procedures that I have converted to mysql .

Originally the oracle stored procudure are in this form :-
This stored procedure is declared inside a package . The following listing is the
package body .

PROCEDURE proc_name(
v_security_id IN VARCHAR,
v_date IN DATE,
v_data OUT var_of_type_refcursor
)
IS
BEGIN
OPEN v_data FOR
SELECT *
FROM my_view
WHERE v_security_id = bond_security_id AND v_date = dated;
END;

The var_of_type_refcursor is a variable of type ref cursor which is declared in the package specification .

In java you can access this Oracle cursor(out parameter) as :-
...
cStmt.registerOutParameter(3, OracleTypes.CURSOR);
rSet = (ResultSet) cStmt.getObject(3);
...

cStmt is a variable of type CallableStatement.

I have now converted this oracle stored procedure to a mysql stored proc like this :-

create PROCEDURE proc_name(
in v_security_id VARCHAR,
in v_date DATE
)

BEGIN
OPEN v_data cursor FOR
SELECT *
FROM my_view
WHERE v_security_id = bond_security_id AND v_date = dated;
END;

I have removed the ref cursor out parameter declared in Oracle , as in Mysql you can cannot pass a cursor as an out parameter.

My question is how can I access this cursor in a java program that is returned by my mysql procedure .

All questions and comments are welcome .

Thanks in advance ,

satyaki