Sending parameters to a SP on Oracle via Access

[MoJe]

Hi out there,

I am trying to make a modul in Access97 to connect and execute a stored procedure on an Oracle database. The connection is up and running, but can anyone help me declare and execute the string that I have to write in order to execute the SP with two parameters? The function most also return an integer.

I have tried various examples, but none of them work.
Please help. Any example code will be greatly appreciated.

Thanks in advance.
Morten

 

[rewdee]

You must be ahead of me. As I understand it, Access cannot execute a stored procedure on a Oracle db.

However, if you can get the code to the stored procedure (and most stored procedures are not that complicated), you can write the VBA code in a module call your own SP.

Hope this helps,
Rewdee

 

[Junior1544]

wouldn't a passthrough query do what you are looking for??

i've never done it, and i don't even have an Oracle db...

--James
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[rewdee]

Sorry but a pass-thru query does not execute a strored procedure on a database. A pass-thru query will return a result without the use of JetEngine.

 

[Quehay]

Whoah, not much useful info being passed along here...

-Yes you can use stored procs.
-You need an ADO Command object to do so

Here's a code snippet (I'm using the Oracle ADO provider rather than the MSDORA--I believe but can't confirm that the MS provider won't do so well with stored procs). There are some hitches with data types (date formats etc.), and I can't recall the particulars off the top of my head. Oracle has a entire user guide for the OLE DB provider, and I'd pursue getting a copy of that.

Here's a code snippet that may helpful (note the braces around the proc name and the use of '?' to indicate parameter):

[tt]
Dim Cmd As New ADODB.Command
Dim Prm As ADODB.Parameter
Dim Rs As ADODB.Recordset

Set Prm = Cmd.CreateParameter("Prm_Study", adBigInt, adParamInput, , p_ST_ID)

Cmd.Parameters.Append Prm

With Cmd

.ActiveConnection = g_Cnn
.CommandType = adCmdText
.Properties("PLSQLRSet&quot = True
.CommandText = "{ CALL your_pack.your_proc(?) }"

End With

Set Rs = Cmd.Execute
[/tt]