MySQL Forums
Forum List  »  Oracle

Oracle Procedure Out parameter as Cursor
Posted by: satyaki roy
Date: March 05, 2007 01:50AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Oracle Procedure Out parameter as Cursor
14950
March 05, 2007 01:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.