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("ExampleDb", "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