Re: Initiating Oracle stored procedures through ODBC

mudhens_at_cyberramp.net (richard castorena) wrote:

>I am currently using MS Access 2.0 to access a Oracle 7.1 database
>using ODBC. I would like to be able to initiate an Oracle stored
>procedure from Access. The only thing I can think of is to use a SQL
>pass-through query to kick it off.
 

>I have tried sending "exec procedure_name" but it always returns
>invalid SQL statement. I guess it is expecting something using a
>Select clause. BTW, I have successfully created other pass-through
>queries so there isn't a problem with the connecting to Oracle.
 

>Any help would be appreciated. Please e-mail me at
>mudhens_at_cyberrramp.net
 

>Thanks, Richard Castorena

I am working with SQL Server 6.0 processing about 30 sp's for reporting purposes and I have found that the SQL for pass through qyery should be as follows: "exec [sp_name] 'param1', 'param2', ...... 'paramN'

Parameters have to follow the native (in your case Oracle) syntax, so that is you need to add Chr$(34) for a " then that will be required.

I am sying all this with the belief that since Oracle as well as SQL Server use ODBC drivers the procedure should be very similar.

Again, I've found a lot of information on the MSDN as well as on the TechNet, it just saves the time from browsing the web. Received on Wed Apr 24 1996 - 00:00:00 CEST