Re: Access and Oracle stored procedures

Thanks for the information. I ran catproc.sql, tried "begin sp_myproc; end;", and found that all is well. I found another way to do it in Microsoft's Visual FoxPro knowledge base and noted it below. Thanks again.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:t6pan6mm2baa7e_at_beta-news.demon.nl...
> Answers embedded
> Sybrand Bakker, Oracle DBA
>
> "Larry MacNeill" <Larry.MacNeill_at_uchsc.edu> wrote in message
> news:94i4cq$f6g$1_at_Crestone.UCHSC.edu...
> > How do you run a stored procedure and a function using Access 2000 and
> ODBC?
>
> IIRC running a pass-thru query with as statement
> begin <procedure>; end;
>
> > I tried this.
> > create or replace procedure sp_commit
> > begin
> > commit;
> > end;
>
> As ODBC auto-commits it would be extreemly redundant to call a procedure
> executing a commit only!

I did this only to make a simple stored procedure with no side effects. (Extremely redundant isn't redundant, is it? :-) );

> > Then I created a pass-through SQL query using Access with this text.
> > execute sp_commit;
>
> execute is a *sqlplus* shortcut for
> begin <procedure name>; end;

I think that "execute" is also standard SQL-92. I've just discovered that Oracle uses "call" instead. So, "call sp_myproc" seems to be right but it braces are also required---"{call sp_myproc}" (without the quotes, of course). Received on Tue Jan 23 2001 - 12:27:42 CST