How do I call an Oracle stored procedure from an Excel VBA script?

by

There’s a lot of plumbing you have to get right in order to get this to work. Try this guide.

It includes this sample. It assumes you’ve made a reference to ADO, you’ve downloaded the OleDB provider for Oracle, and you’ve set up the TNSNames.ora file. If you don’t want to use TNSNames.ora you can try an alternative connection string 

Dim Oracon As ADODB.Connection
Dim recset As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim objErr As ADODB.Error

Dim Message, Title, Default, DeptValue
Message = "Enter a department number (10, 20, or 30)"
Title = "Choose a Department"
Default = "30"

On Error GoTo err_test
DeptValue = InputBox(Message, Title, Default)
If DeptValue = "" Then Exit Sub
If DeptValue < 10 Or DeptValue > 30 Then DeptValue = 30

Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                          "Data Source=exampledb;" & _
                          "User ID=scott;" & _
                          "Password=tiger;"  
Oracon.Open
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = Oracon
Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
                                DeptValue)
cmd.Parameters.Append param1
Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
cmd.Parameters.Append param2

' Enable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = TRUE  

cmd.CommandText = "{CALL Employees.GetEmpRecords(?, ?)}"
Set recset = cmd.Execute

' Disable PLSQLRSet property
Cmd.Properties ("PLSQLRSet") = FALSE 

Do While Not recset.EOF
   MsgBox "Number: " & recset.Fields("empno").Value & "  Name: " &
   recset.Fields("ename").Value & "  Dept: " & recset.Fields("deptno").Value
   recset.MoveNext
Loop

Exit Sub

err_test:
    MsgBox Error$
    For Each objErr In Oracon.Errors
        MsgBox objErr.Description
    Next
    Oracon.Errors.Clear
    Resume Next

If you run into problems you can come back and ask a more specific question.

More Related Contents:

  1. SQL Query output in VBA is different than in SQL Oracle
  2. Paging with Oracle
  3. Performing SQL queries on an Excel Table within a Workbook with VBA Macro
  4. Using Excel VBA to export data to MS Access table
  5. Accessing SQL Database in Excel-VBA
  6. Excel function to make SQL-like queries on worksheet data?
  7. Call a stored procedure with another in Oracle
  8. Using Excel VBA to run SQL query
  9. Solution to “cannot perform a DML operation inside a query”?
  10. Array in IN() clause oracle PLSQL
  11. Inserting into Oracle and retrieving the generated sequence ID
  12. How to use Alias in Where clause? [duplicate]
  13. Local Temporary table in Oracle 10 (for the scope of Stored Procedure)
  14. LISTAGG in Oracle to return distinct values
  15. Fetch the row which has the Max value for a column
  16. Oracle SQL: Update a table with data from another table
  17. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  18. Stored procedure or function expects parameter which is not supplied
  19. Concatenate and group multiple rows in Oracle [duplicate]
  20. Difference between BYTE and CHAR in column datatypes
  21. How do I execute a stored procedure once for each row returned by query?
  22. SQL to generate a list of numbers from 1 to 100
  23. How to use BOOLEAN type in SELECT statement
  24. How to show all privileges from a user in oracle?
  25. Why can’t I seem to force Oracle 11g to consume more CPUs for a single SQL query
  26. Oracle (Old?) Joins – A tool/script for conversion?
  27. Return number from Oracle Select statement after parsing date
  28. How do you interpret a query’s explain plan?
  29. How to call Oracle MD5 hash function?
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment