Re: Don't understand why this fails.

Jeff Kish wrote:

> Greetings.
>
> I am trying to use sqlplus to compile a stored procedure on Oracle 8.1.7.
> Greetings.
>
> I am getting an error when I try, in sql plus, to compile a stored procedure on my system.
> Could someone explain it? I know it says: " insufficient privileges or does not exist", but I think the select query
> shows it does exist, and further, I am logged in as schema owner, so that should give me the rights.
>
> Thanks for your guidence,
> Jeff Kish
>
> (Here is my sql plus session)
>
> SQL> connect TheSystem/TheSystem
> Connected.
> SQL> select * from user_source where
> 2 name like 'MXO_MM2%' and rownum < 2;
>
> NAME TYPE LINE
> ------------------------------ ------------ ----------
> TEXT
> --------------------------------------------------------------------------------
> MXO_MM2_ITM_SP PROCEDURE 1
> PROCEDURE MXO_MM2_ITM_SP(ItmRec IN OUT MAX_MM2_INTERFACE%RowType)
>
> SQL> SET SERVEROUTPUT ON
> SQL> BEGIN
> 2 DBMS_OUTPUT.ENABLE(2000000);dbms_ddl.alter_compile( 'PROCEDURE', 'TheSystem', 'MXO_MM2_ITM_SP' );
> 3 END;
> 4 /
> BEGIN
> *
> ERROR at line 1:
> ORA-20000: Unable to compile PROCEDURE "TheSystem"."MXO_MM2_ITM_SP", insufficient
> privileges or does not exist
> ORA-06512: at "SYS.DBMS_DDL", line 73
> ORA-06512: at line 2
>
> SQL>
The error message means exactly what it says. The procedure references some object, most likely a table or view, for which you do not have sufficient rights to do what the procedure is trying to do ... select, insert, update, delete, or reference.

If the rights to the object are provided by a role that is insufficient for a procedure. The rights must be explicitly granted to the schema.

Daniel Morgan Received on Wed Mar 19 2003 - 14:22:48 CST