CreatePLSQLDynaset Method

Applies To

Deprecated.

For information on how to perform these tasks, see "Returning PL/SQL Cursor Variables".

Description

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.

Usage

set OraDynaset = CreatePLSQLDynaset(SQLStatement, CursorName, options)

Arguments

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.

Constants

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.

Remarks

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.

Examples

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