I am getting the error when I use the below command in SQL;
EXEC SYS.DBMS_SESSION.SLEEP(1);
But If I use the DBMS_LOCK then it's working.
EXEC SYS.DBMS_LOCK.SLEEP(1);
ref : https://oracle-base.com/articles/18c/dbms_session-sleep-18c
is there something I am missing while using this command ?
DBMS_LOCK is a privileged routine, ie, there is a lot of power in it, and thus it typically was protected from general use. The problem is, the very useful SLEEP routine was also then not available.
In 18c, we realised this problem and so replicated the SLEEP routine to DBMS_SESSION which is publicly available. Thus everyone gets to use SLEEP.
Before 18c, you need to grant execute on DBMS_LOCK to those that want it (which is risky), or create a wrapper function and only grant that, eg
create or replace
procedure SYS.SLEEP(n number) is
begin
dbms_lock.sleep(n);
end;
grant execute on sys.sleep to public;