Call Oracle Procedure from Excel macro

Hi ALL
I need to call a Oracle Procedure from Excel Macro?
Does anybody know How to do it?
Any code samples?
My procedure has 5 IN parameters and 1 OUT parameter.
I was thinking of using ODBC to connect to the database.
Please help...........urgent.........
Thanks

I can't answer all of your questions for Oracle, but I have successfully done
this with PeopleSoft using ADO. I hope the following will set you on track
for looking at the appropriate arguments at least:

Set ADO "CommandText" to your stored procedure name and then set the
"CommandType" to "adCmdStoredProc"

e.g:
cnADO.CommandText = myStoredProcedureName
cnADO.CommandType = adCmdStoredProc

You don't specify if you are returning records or not; my example returns
records. So your code might look something like:

Dim adoCM As ADODB.Command
Dim adoCN As ADODB.Connection
Dim adoRS As ADODB.Recordset

Set adoCN = New ADODB.Connection
adoCN.CursorLocation = adUseClient
adoCN.Properties("Prompt") = adPromptComplete '<this is for logon not param

adoCN.Open "ODBC;YOUR_ConnectionString_for_Oracle;"

Set adoCM = New ADODB.Command
Set adoCM.ActiveConnection = adoCN
adoCM.CommandText = YourProcedureName
adoCM.CommandType = adCmdStoredProc

'Run query and populate a recordset
Set adoRS = New ADODB.Recordset
Set adoRS = adoCM.Execute

In PeopleSoft I was able to build-in the prompts into the stored procedure
on the PeopleSoft side, I don't know how to do that for Oracle or if you even
can...

HTH somehow.

"test" wrote:

> Hi ALL
> I need to call a Oracle Procedure from Excel Macro?
> Does anybody know How to do it?
> Any code samples?
> My procedure has 5 IN parameters and 1 OUT parameter.
> I was thinking of using ODBC to connect to the database.
> Please help...........urgent.........
> Thanks
>
>
>

Also, there is a product from Oracle called "Oracle Objects for OLE". It's
downloadable form Oracle. It allows you to attach to the database and
retrieve data, change data, run procedures, etc.

I haven't actually used it to run procedures, yet, but it says that it can.

Here's a link to Oracle's description of it:

http://www.oracle.com/technology/tec...4O_O9i_FO.html

Thanks for your input guys.
But still need someone to tell me hoe to call a sp from excel.





"mark" <[email protected]> wrote in message
news:[email protected]...
> Also, there is a product from Oracle called "Oracle Objects for OLE".
It's
> downloadable form Oracle. It allows you to attach to the database and
> retrieve data, change data, run procedures, etc.
>
> I haven't actually used it to run procedures, yet, but it says that it
can.
>
> Here's a link to Oracle's description of it:
>
> http://www.oracle.com/technology/tec...4O_O9i_FO.html

This might need some intelligent editing for Oracle, but here you go:

'back in the database DO THE FOLLOWING
'CREATE PROC spTemp(@Table1 nvarchar(50), @Table2 nvarchar(50))
'as
'-- example of a dynamic SQL sp returning multiple recordsets
'SET NOCOUNT ON
'EXEC('SELECT * FROM ' + @Table1)
'EXEC('SELECT * FROM ' + @Table2)
'SET NOCOUNT OFF
'GO

Sub Test2()
Dim vParams As Variant
Dim vValues As Variant
Dim rsReturn As ADODB.Recordset
vParams = Array("Table1", "Table2")
vValues = Array("TableName1", "TableName2")
'change DBNAME to whatever DB you created the above proc in
ReturnRSFromSP "spTemp", vParams, vValues, "DBNAME"
End Sub

Public Sub ReturnRSFromSP(strSP As String, _
vParams As Variant, _
vValues As Variant, _
strCatalog As String)

Dim cnSP As ADODB.Connection
Dim cmdSP As ADODB.Command
Dim lCounter As Long
Dim strItem As String
Dim lIndex As Long
Dim rsReturn As ADODB.Recordset

Set cnSP = New ADODB.Connection

'you will have to amend this for Oracle
cnSP.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=" & strCatalog & _
";Data Source=" & FILLTHISIN 'add your data source here

cnSP.Open

'create the command object
Set cmdSP = New ADODB.Command
cmdSP.ActiveConnection = cnSP
cmdSP.CommandText = strSP
cmdSP.CommandType = adCmdStoredProc
cmdSP.Parameters.Refresh

lCounter = 0

For lCounter = 1 To cmdSP.Parameters.Count - 1

strItem = cmdSP.Parameters(lCounter).Name

For lIndex = 0 To UBound(vParams)

If "@" & vParams(lIndex) = strItem Then

cmdSP.Parameters(lCounter).Value = vValues(lIndex)
Exit For

End If

Next

Next

'*****************************************
'use this bit if trying to return results as a recordset
'delete it otherwise
'*****************************************

'create the recordset object
Set rsReturn = New ADODB.Recordset

With rsReturn

.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic

'execute the SP returning the result into a recordset
.Open cmdSP

End With

Do Until rsReturn Is Nothing

If rsReturn.State = adStateOpen Then

DumpRecordset rsReturn

End If

Set rsReturn = rsReturn.NextRecordset

Loop
'*********************end of section

'*****************************************
'use this bit if just trying to run a stored proc
'delete it otherwise
'*****************************************
'execute the SP
oCmd.Execute

'*********************end of section


Set cmdSP = Nothing

If cnSP.State = adStateOpen Then
cnSP.Close
End If
Set cnSP = Nothing
Set rsReturn = Nothing
End Sub

Sub DumpRecordset(rsName As ADODB.Recordset, Optional lstartpos As Long)
Dim W As Workbook
Dim nField As Integer
Dim lRowPos As Long

Set W = ActiveWorkbook

Workbooks.Add

With rsName

For nField = 1 To .Fields.Count

Cells(1, nField).Value = .Fields(nField - 1).Name

Next nField

If .RecordCount = 0 Then Exit Sub
.MoveFirst

If Not IsEmpty(lstartpos) Then .Move lstartpos

End With

Cells(2, 1).CopyFromRecordset rsName
End Sub

Robin Hammond
www.enhanceddatasystems.com

"test" <test2> wrote in message
news:[email protected]...
> Thanks for your input guys.
> But still need someone to tell me hoe to call a sp from excel.
>
>
>
>
>
> "mark" <[email protected]> wrote in message
> news:[email protected]...
>> Also, there is a product from Oracle called "Oracle Objects for OLE".
> It's
>> downloadable form Oracle. It allows you to attach to the database and
>> retrieve data, change data, run procedures, etc.
>>
>> I haven't actually used it to run procedures, yet, but it says that it
> can.
>>
>> Here's a link to Oracle's description of it:
>>
>> http://www.oracle.com/technology/tec...4O_O9i_FO.html
>
>