Re: ERROR: PLS-00201
Date: Sat, 15 Aug 1998 14:16:06 GMT
Message-ID: <35d697fb.2282421_at_192.86.155.100>
A copy of this was sent to "John A. Kroll" <krollj_at_worldnet.att.net> (if that email address didn't require changing) On 15 Aug 1998 01:14:22 GMT, you wrote:
> While porting some PL/SQL from 7.1 to 7.3 I ran across the
>following problem (simplified example):
>
>PROCEDURE send_change_alert( alert_msg IN VARCHAR )
>IS
>BEGIN
> DBMS_ALERT.SIGNAL( 'test_signal', alert_msg );
>END send_change_alert;
>
>Which results in the following error:
>
>ERROR: PLS-00201: identifier 'SYS.DBMS_ALERT' must be declared
>
>But now if I run the following in sqlplus,
>
>begin
> DBMS_ALERT.SIGNAL( 'test_signal', 'This test works.' );
>end;
>/
>
>Everything works as expected. A check of the synonyms shows:
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant execute on dbms_alert to <OWNER>;
>SQL> select * from dba_synonyms where synonym_name = 'DBMS_ALERT';
>
>OWNER SYNONYM_NAME
>------------------------------ ------------------------------
>TABLE_OWNER TABLE_NAME
>------------------------------ ------------------------------
>DB_LINK
>----------------------------------------------------------------------------
>
>----
>PUBLIC DBMS_ALERT
>SYS DBMS_ALERT
>
>Has anyone seen this before? What am I missing here?
>
>Thanks in advance,
>John Kroll
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Aug 15 1998 - 16:16:06 CEST