excel调用oracle,sql – 如何从Excel VBA脚本调用Oracle存储过程？

为了让这个工作起来,你需要做很多管道工作.试试这个

guide.

它包括这个样本.它假设您已经引用了ADO,您已经下载了Oracle的OleDB提供程序,并且您已经设置了TNSNames.ora文件.如果您不想使用TNSNames.ora,可以尝试替代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

如果您遇到问题,可以回来询问更具体的问题.