Why can’t I grant exec on dbms_lock.sleep() OR create a procedure using it (but I can run it fine on its own)

oracle-11goraclexe

I am trying to write a small bit of PL/SQL that has a non-CPU burning sleep in it.

The following works in sqldeveloper

begin
  dbms_lock.sleep(5);
end;

BUT (as the same user), I can't do the following:

create or replace
procedure sleep(seconds in number)
is
begin
  dbms_lock.sleep(seconds);
end;

without the error "identifer "DBMS_LOCK" must be declared… Funny as I could run it without a procedure.

Just as strange, when I log in as a DBA, I can run the command

grant exec on dbms_lock to public;

and I get 

ERROR at line 1:
ORA-00990: missing or invalid privilege

This is oracle version "Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production"

Best Answer

[richard@f1 ~]$ sqlplus /nolog 

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 14 13:33:18 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect sys@xe as sysdba

Enter password: ******

Connected.
SQL> grant execute on sys.dbms_lock to richard;

Grant succeeded.