如何从 Excel VBA 脚本调用 Oracle 存储过程? [英] How do I call an Oracle stored procedure from an Excel VBA script?

问题描述

我在 Oracle 数据库中有一个带参数的存储过程.

I have a stored procedure with parameters in an Oracle database.

如何从 Excel VBA 脚本中调用它?

How do I call it from within 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.

它包含此示例.它假设您已经引用了 ADO，您已经下载了 Oracle 的 OleDB 提供程序，并且您已经设置了 TNSNames.ora 文件.如果您不想使用 TNSNames.ora，您可以尝试替代 连接字符串

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.

这篇关于如何从 Excel VBA 脚本调用 Oracle 存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！