Installation guide

5. mxODBC Overview
SELECT @a * 3;
Oracle Ref Cursors as Output Parameters
Oracle has the concept of reference cursors, which provide a similar way to pass
cursors to the stored procedure caller. Instead of defining an output variable or a
set of output variables, you simply define a REF CURSOR as output variable in
your stored procedure.
You can then access the open cursor after calling the stored procedure by simply
using the standard
.fetch*() and .nextset() APIs to access the results. The
key to making this work is by not passing in any variable for the output REF
CURSOR when calling the stored procedure.
8
Oracle Base has a more detailed article on this:
• Using Ref Cursors To Return Recordsets
Example:
CREATE PROCEDURE sp_result_set
(a INTEGER,
rs OUT SYS_REFCURSOR)
AS
BEGIN
OPEN rs FOR
SELECT a * 3 FROM DUAL;
END;
Note that you don't need to call the procedure with the second parameter. The
result set will still be available.
IBM DB2 Cursors in Stored Procedures
IBM has a similar feature to the Oracle Ref Cursors. You simply declare a cursor
and open it before returning from the stored procedure. The cursor is then
available for reading in Python.
Example:
CREATE PROCEDURE sp_result_set
(a INTEGER)
RESULT SETS 1
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT a * 3 FROM SYSIBM.SYSDUMMY1;
OPEN c1;
END
PostgreSQL Cursors in Stored Procedures
PostgreSQL also allow opening cursors in stored procedures which are then
available in Python via mxODBC's
cursor.fetch*() methods.
Example:
CREATE FUNCTION sp_result_set
8
Thanks to Etienne Desgagné for pointing out this solution.
75