RecordSet Property Example

This example demonstrates setting recordset property to external dynaset created by CreatePlsqlDynaset method. This example returns PLSQL cursor as a external dynaset for the different values of DEPTNO parameter. Make sure that corresponding stored procedure (found in EMPCUR.SQL) is available in the Oracle Server. Copy this code into the definition section of a form containing the Oracle Data Control named oradata1, then press F5

Sub Form_Load ()

'Declare variables as OLE Objects.

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&)

' Now attach the Oradynaset to Data control's recordset.

set oradata1.recordset = OraDynaset

'

'

' Do some operation

'

'

' Now set the deptno value to 20

OraDatabase.Parameters("DEPTNO").Value = 20

'Refresh the sqlstmt

Oradata1.recordset.Refresh

'Remove the parameter.

OraDatabase.Parameters.Remove ("DEPTNO")

End Sub