Returning PL/SQL Cursor Variables

PL/SQL cursor variables are mainly used for accessing one or more query result sets from PL/SQL blocks and stored procedures and functions. The OraParameter object in OO4O can be used to hold a PL/SQL cursor variable.

The OraParameter object representing a cursor variable should be of type ORATYPE_CURSOR, and can only be defined as an output variable. After the PL/SQL block is executed, the Value property of OraParameter object contains a read-only OraDynaset object. This OraDynaset object can be used to scroll through the returned rows.

CREATE PACKAGE EmpAndDept AS

cursor emp is select * from emp;

cursor dept is select * from dept;

TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;

TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

PROCEDURE GetEmpAndDeptData (emp_cv OUT EmpCurTyp,

dept_cv OUT DeptCurTyp);

END EmpAndDept;

CREATE PACKAGE BODY EmpAndDept AS

PROCEDURE GetEmpAndDeptData (emp_cv OUT EmpCurTyp,

dept_cv OUT DeptCurTyp) IS

BEGIN

OPEN emp_cv FOR SELECT * FROM emp;

OPEN dept_cv FOR SELECT * FROM dept; END GetEmpAndDeptData;

END EmpAndDept;

The following example executes the previously-created procedure to get the cursors for emp and dept tables.

Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")

Set EmpDb = OO4OSession.OpenDatabase("HRDB", "scott/tiger", 0)

empDb.Parameters.Add "EMPCUR", 0, ORAPARM_OUTPUT

empDb.Parameters("EMPCUR").serverType = ORATYPE_CURSOR

empDb.Parameters.Add "DEPTCUR", 0, ORAPARM_OUTPUT

empDb.Parameters("DEPTCUR").serverType = ORATYPE_CURSOR

Set PlSqlStmt = empDb.CreateSql("Begin EmpAndDept.GetEmpAndDeptData

(:EMPCUR, :DEPTCUR); end;", 0)

Set EmpDynaset = empDb.Parameters("EmpCur").Value

Set DeptDynaset = empDb.Parameters("DeptCur").Value

MsgBox EmpDynaset.Fields("ENAME").Value

MsgBox DeptDynaset.Fields("DNAME").Value