oracle pl/sql DBMS_LOCK error

Question

In the Oracle PL/SQL, I want to test the sleep function. I am using hr schema. but it gives me error :

PLS-00201: identifier 'DBMS_LOCK' must be declared

code:

begin
 DBMS_LOCK.Sleep( 60 );
end;
/

Answer 1

You should grant execute on that package to your schema

grant execute on <object> to <user>;

e.g.

connect as sys
grant execute on SYS.DBMS_LOCK to someuser;

Answer 2

If you you don't have access to sys or your dba is unwilling to do

   GRANT EXECUTE on SYS.DBMS_LOCK to you;

You can create a Java procedure in the database:

   CREATE OR REPLACE PROCEDURE SLEEPIMPL (P_MILLI_SECONDS IN NUMBER) 
   AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';

And create a function, which calls the java stored procedure

 CREATE OR REPLACE FUNCTION sleep (
    seconds IN NUMBER
   ) RETURN NUMBER
   AS
   BEGIN
     SLEEPIMPL( seconds );
     RETURN seconds;
   END;

which after a

GRANT EXECUTE ON sleep TO public;

you can call from a select

select sleep(6000) from dual