Re: PL/SQL and DBMS_LOCK mystery

Les Neste <lesneste_at_mindspring.com> wrote in message news:38e32b85.142623378_at_news.mindspring.com...
> It's probably not a mystery to you but it is to me. I'm RTFMing as we
> speak but I'd appreciate any help.
>
> I want to do is call procedures provided in the oracle-supplied
> DBMS_LOCK package from within a procedure which I define in a package
> of my own. For some reason, this is causing a PL/SQL compilation
> error. Is there something I have to do to make DBMS_LOCK available to
> my own package?
>
> In outline, here's what I'm doing.
>
> CREATE PACKAGE BODY my_pkg
> ...
> CREATE PROCEDURE my_proc
> ...
> DBMS_LOCK.SLEEP( 100 );
> ...
> END my_proc;
> ...
> END my_pkg;
>
> If I comment out the DBMS_LOCK call, it compiles fine. If I include
> it, I get the compilation error.
>
> Looks like I'll be buying some PL/SQL tool like Toad to give me a
> little more feedback than Oracle's "warning: package created with
> compilation errors". Any opinions about tools would be welcome as
> well.
>

What about using the tool 'select * from [dba_|user_]errors'? You don't include the error message, yet I have a feeling what it is, so I will press the red button before the quizmaster completes his question... It's roles: roles are not enabled during stored procedures. You'll either need to grant directly
(Oracle 7, Oracle 8.0) or run the procedure with invokers rights (Oracle 8i). Refer to http://osi.oracle.com/~tkyte for further details about the latter feature.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Mar 30 2000 - 13:07:37 CEST