Re: Initiating Oracle stored procedures through ODBC

In article <31732B08.743A_at_iadfw.net>, kbarnes_at_iadfw.net says...
>
>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.
 {snip}
>If you are trying to duplicate the following from SQL*Plus
>execute sp;
>Try this
>begin sp; end;
>This works for us just fine under 7.1 and 7.2

We are having a similar problem, but calling stored procedures from VB via ODBC. It is possible to execute a procedure using the type of mechanism above, and even to make it take input parameters e.g. ...
MySQL = "{call temppro ('" + MyParam + "')}" MyDB.Execute MySQL, 64
...

Which works fine, (this uses the ODBC 'call' rather than SQL pass through). The problem is that we can't get any information back from the procedure, not even an exit status to indicate whether it has executed successfully.

Does anyone know how we can get either IN/OUT parameter values or the return code from PL/SQL functions from within VB? Using a message table is always an option, but it just seems so kludgey.

TIA, email replies preferred.
Martin. Received on Wed Apr 17 1996 - 00:00:00 CEST