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

'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