Oracle Objects for OLE
Release 9.2

Part Number A95895-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents

Master Index

Feedback

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


 
Oracle
Copyright © 1994, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents

Master Index

Feedback