Re: Access and Oracle stored procedures

Answers embedded

Hth,

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;

> Access 97 information would be useful, too.

Ditto

> What is the Procedural Option referred to in the error message below?

This is PL/SQL

 I
> have looked at the master index but do not see it. I do not see the
 PL/SQL
> banner when I start SQL*Plus.
>
> Are there any books that
>
> 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!

> 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;
> The error message is
> [Oracle][Oracle ODBC Driver][Oracle (#900)
>
> From the documentation,
> -----------------------------
>
> ORA-00900 invalid SQL statement
>
>
> Cause: The statement is not recognized as a valid SQL statement. This
 error
> can occur if the Procedural Option is not installed and a SQL statement
 is
> issued that requires this option (for example, a CREATE PROCEDURE
> statement). You can determine if the Procedural Option is installed by
> starting SQL*Plus. If the PL/SQL banner is not displayed, then the option
 is
> not installed. Action: Correct the syntax or install the Procedural
 Option.
>
> -----------------------------
>
> Any clues are welcome. Thanks.
>
>

To verify the existence of the procedural option execute select * from v$option.

To install the procedure option start svrmgrl, connect / as sysdba (or connect internal) and run
%ORACLE_HOME%\rdbms<xx>\admin\catproc.sql

>
>
>
Received on Mon Jan 22 2001 - 15:51:29 CST