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

问题描述

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

解决方案

为了让这个工作，你必须得到很多管道。请尝试此指南。

它包含这个示例。它假设您已经参考了ADO，您已经下载了Oracle的OleDB提供程序，并且您已经设置了TNSNames.ora文件。如果您不想使用TNSNames.ora，您可以尝试另一种连接字符串

  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 
消息=输入部门号码（10,20或30）
标题=选择部门
默认=30
 
错误GoTo err_test 
 DeptValue = InputBox（消息，标题，默认）
如果DeptValue =然后退出Sub 
如果DeptValue< 10或DeptValue> 30 Then DeptValue = 30 
 
 Set Oracon = CreateObject（ADODB.Connection）
 Oracon.ConnectionString =Provider = OraOLEDB.Oracle; &安培; _ 
Data Source = exampledb; &安培; _ 
用户ID = scott; &安培; _ 
密码=虎 
 Oracon.Open 
设置cmd =新建ADODB.Command 
设置cmd.ActiveConnection = Oracon 
设置param1 = cmd.CreateParameter（param1，adSmallInt，adParamInput，
 DeptValue）
 cmd.Parameters.Append param1 
设置param2 = cmd.CreateParameter（param2，adSmallInt，adParamOutput）
 cmd.Parameters.Append param2 
 
'启用PLSQLRSet属性
 Cmd.Properties（PLSQLRSet）= TRUE 
 
 cmd.CommandText ={CALL Employees.GetEmpRecords（？？）}
设置recset = cmd.Execute 
 
'禁用PLSQLRSet属性
 Cmd.Properties（PLSQLRSet）= FALSE 
 
尽管没有recset.EOF 
 MsgBox编号：& recset.Fields（empno）。Value& 名字：& 
 recset.Fields（ename）。Value& 部门：& recset.Fields（deptno）。值
 recset.MoveNext 
循环
 
退出子
 
 err_test：
 MsgBox错误$ 
对于每个objErr在Oracon.Errors 
 MsgBox objErr.Description 
下一个
 Oracon.Errors.Clear 
简历下一个
  

如果遇到问题，可以回来问一个更具体的问题。

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

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.

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屋！