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

Call Oracle Procedure from Excel macro
 

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

Call Oracle Procedure from Excel macro
 

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

Call Oracle Procedure from Excel macro
 

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





"mark" wrote in message
...
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