Status Property Example
This example demonstrates the use of parameters and ExecuteSQL to call a
Stored Procedure (located in ORAEXAMP.SQL). After calling the Stored Procedure, the
Status property of each parameter is checked. Copy and paste this code into the
definition section of a form. Then press F5.
Sub Form_Load ()
'Declare variables as OLE Objects.
Dim OraSession As Object
Dim OraDatabase As Object
Dim OraDynaset As Object
'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&)
'Add EMPNO as an Input parameter and set its initial value.
OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT
'Add ENAME as an Output parameter and set its initial value.
OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT
'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.
' This Stored Procedure is located in the file ORAEXAMP.SQL.
OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")
If OraDatabase.Parameters("EMPNO").Status & ORAPSTAT_INPUT Then
MsgBox "Parameter EMPNO used for input."
End If
If OraDatabase.Parameters("ENAME").Status & ORAPSTAT_OUTPUT Then
MsgBox "Parameter ENAME used for output."
End If
'Display the employee number and name.
MsgBox OraDatabase.Parameters("EMPNO").value
MsgBox OraDatabase.Parameters("ENAME").value
'Remove the Parameters.
OraDatabase.Parameters.Remove "EMPNO"
OraDatabase.Parameters.Remove "ENAME"
End Sub