ExecuteSQL Method Example

This example uses the Add and Remove parameter methods, the ServerType parameter property, and the ExecuteSQL database method to call a Stored Procedure and Function (located in ORAEXAMP.SQL). Copy 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 OraDatabase

'Create the OraSession Object.

Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'Create the OraDatabase Object.

Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

'Add EMPNO as an Input/Output parameter and set its initial value.

OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT

OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER

'Add ENAME as an Output parameter and set its initial value.

OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT

OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2

'Add SAL as an Output parameter and set its initial value.

OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT

OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER

'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.

' This Stored Procedure can be found in the file ORAEXAMP.SQL.

OraDatabase.ExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")

'Display the employee number and name.

'Execute the Stored Function Employee.GetSal to retrieve SAL.

' This Stored Function can be found in the file ORAEXAMP.SQL.

OraDatabase.ExecuteSQL ("declare SAL number(7,2); Begin :SAL:=Employee.GetEmpSal (:EMPNO); end;")

'Display the employee name, number and salary.

MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & OraDatabase.Parameters("EMPNO").value & ",Salary=" & OraDatabase.Parameters("SAL").value

'Remove the Parameters.

OraDatabase.Parameters.Remove "EMPNO"

OraDatabase.Parameters.Remove "ENAME"

OraDatabase.Parameters.Remove "SAL"

End Sub