Deprecated.
For information on how to perform these tasks, see "Returning PL/SQL Cursor Variables".
Creates a dynaset from a PL/SQL cursor. The SQL statement should be a stored procedure or an anonymous block. The resulting dynaset is read-only and attempting to set SQL property results in an error. Dynasets can be refreshed with new parameters similar to dynasets without cursors.
| Arguments | Description |
|---|---|
SQLStatement |
Any valid Oracle PL/SQL stored procedure or anonymous block. |
CursorName |
Name of the cursor created in the PL/SQL stored procedure. |
options |
A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. |
The options flag values are:
| Constant | Value | Description |
|---|---|---|
ORADYN_DEFAULT |
&H0& |
Accept the default behavior. |
ORADYN_NO_BLANKSTRIP |
&H2& |
Do not remove trailing blanks from character string data retrieved from the database. |
ORADYN_NOCACHE |
&H8& |
Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage. |
ORADYN_NO_MOVEFIRST |
&H40& |
Do not force a MoveFirst operation when the dynaset is created. BOF and EOF are both true. |
These values can be found in the oraconst.txt file.
The SQL statement must be a PL/SQL stored procedure with BEGIN and END statements around the call, as if it were executed as an anonymous PL/SQL block; otherwise an error is returned. The CursorName argument should exactly match the cursor created inside the stored procedure or anonymous PL/SQL block; otherwise, an error is returned. Cursors created inside the stored procedure should represent a valid SQL SELECT statement.
You do not need to bind the PL/SQL cursor variable using the OraParameters.Add method if the stored procedure returns a cursor as a output parameter. You can still use PL/SQL bind variables in conjunction with the OraParameters collection.
This method automatically moves to the first row of the created dynaset.
Specifying the ORADYN_READONLY, ORADYN_ORAMODE, ORADYN_NO_REFETCH, or ORADYN_DIRTY_WRITE options have no effect on the dynaset creation.
This example demonstrates the use of PL/SQL cursor in the CreatePlsqlDynaset method and Refresh method. This example returns a PL/SQL cursor as a dynaset for the different values of the DEPTNO parameter. Make sure that corresponding stored procedure (found in EMPCUR.SQL) is available in the Oracle database. and paste this code into the definition section of a form. Then, press F5.
Sub Form_Load ()
'Declare variables
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
' Create the Deptno parameter
OraDatabase.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT
OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER
' Create OraDynaset based on "EmpCursor" created in stored procedure.
Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData" & _
"(:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&)
'Should display KING
MsgBox OraDynaset.Fields("ENAME").Value
'Should display 7839
MsgBox OraDynaset.Fields("EMPNO").Value
' Now set the deptno value to 20
OraDatabase.Parameters("DEPTNO").Value = 20
'Refresh the dynaset
OraDynaset.Refresh
'Should display JONES
MsgBox OraDynaset.Fields("ENAME").Value
'Should display 7566
MsgBox OraDynaset.Fields("EMPNO").Value
'Remove the parameter.
OraDatabase.Parameters.Remove ("DEPTNO")
End Sub