How to Call an Oracle Stored Procedure from an Excel Vba Script

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

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.

Call Oracle Stored Procedure with Parameter from Excel

Problem is the Date value and wrong syntax.

Try this one:

cmd.CommandText = "begin proc_name (TO_DATE('" & Format(Range("A1").Value, "yyyy-mm-dd") & "','YYYY-MM-DD')) ; END; "

Or even better try prepared statements with bind parameter, like this:

cmd.CommandText = "begin proc_name(?); END;"
cmd.Parameters.Append cmd.CreateParameter("prm_dt", adDate, adParamInput, , Range("A1").Value)

Or try this syntax: cmd.CommandText = "{CALL proc_name(?)}"

Calling an oracle stored procedure with no parameters ADODB

Transform your procedure to a function:

CREATE OR REPLACE FUNCTION Get_Data RETURN SYS_REFCURSOR IS
res SYS_REFCURSOR;

BEGIN

OPEN OUTPUT FOR 
SELECT DISTINCT
       B.ITEM_ID
       B.ITEM_DESC
    FROM ITEM_FILE B
WHERE ITEM_ID IS NOT NULL
ORDER BY B.ITEM_ID;  
RETURN res;

END Get_Data;
/

Call in VBA would be as this:

cmd.CommandText = "{CALL Get_Data()}"
cmd.Properties("PLSQLRSet") = True
Set Rst1 = cmd.Execute 
cmd.Properties("PLSQLRSet") = False

Note, by default an OUT parameters are used like this:

cmd.Parameters.Append cmd.CreateParameter("OUTPUT", adVarChar, adParamOutput, 100)

However, for RefCursor parameters you must not declare them with cmd.Parameters.Append.

Have a look at Provider for OLE DB Developer's Guide, it contains several examples. Chapter Stored Procedures and Functions Returning Rowsets should be the most relevant for you.

---

Related Topics