This example demonstrates the use of PL/SQL cursor in the CreatePlsqlDynaset
method and Refresh method.
This example returns PL/SQL cursor as a dynaset for the different values of
DEPTNO parameter.
Make sure that corresponding stored procedure (found in EMPCUR.SQL) is
available in the Oracle Server.
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 sqlstmt
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
