Sleep function in ORACLE

Question

I need execute an SQL query in ORACLE it takes a certain amount of time   So I wrote this function   CREATE OR REPLACE FUNCTION MYSCHEMA TEST SLEEP   TIME   IN  NUMBER   RETURN INTEGER IS  BEGIN    DBMS LOCK sleep seconds   gt  TIME    RETURN 1   EXCEPTION    WHEN OTHERS THEN    RAISE     RETURN 1  END TEST SLEEP    and I call in this way  SELECT TEST SLEEP 10 5  FROM DUAL   but to work I need set grant of DBMS LOCK to the owner of the procedure   How I can rewrite this function without using  the DBMS LOCK sleep function

User · Answer

You can use DBMS PIPE SEND MESSAGE with a message that is too large for the pipe  for example for a 5 second delay write XXX to a pipe that can only accept one byte using a 5 second timeout as below  dbms pipe pack message  XXX    lt br gt  dummy  dbms pipe send message  TEST PIPE   5  1     But then that requires a grant for DBMS PIPE so perhaps no better

User · Answer

If executed within  sqlplus   you can execute a host operating system command  sleep      sleep 1   or  host sleep 1

User · Answer

There is a good article on this topic  PL SQL  Sleep without using DBMS LOCK that helped me out  I used Option 2 wrapped in a custom package  Proposed solutions are   Option 1  APEX UTIL sleep If APEX is installed you can use the procedure    PAUSE    from the publicly available package APEX UTIL  Example        Wait 5 seconds      SET SERVEROUTPUT ON   BEGIN     DBMS OUTPUT PUT LINE  Start      to char SYSDATE   YYYY-MM-DD HH24 MI SS         APEX UTIL PAUSE 5       DBMS OUTPUT PUT LINE  End        to char SYSDATE   YYYY-MM-DD HH24 MI SS     END       Option 2  java lang Thread sleep An other option is the use of the method    sleep    from the Java class    Thread     which you can easily use through providing a simple PL SQL wrapper procedure  Note  Please remember  that    Thread sleep    uses milliseconds   --- create --- CREATE OR REPLACE PROCEDURE SLEEP  P MILLI SECONDS IN NUMBER   AS LANGUAGE JAVA NAME  java lang Thread sleep long     --- use --- SET SERVEROUTPUT ON   BEGIN     DBMS OUTPUT PUT LINE  Start      to char SYSDATE   YYYY-MM-DD HH24 MI SS         SLEEP 5   1000       DBMS OUTPUT PUT LINE  End        to char SYSDATE   YYYY-MM-DD HH24 MI SS     END

User · Answer

It would be better to implement a synchronization mechanism  The easiest is to write a file after the first file is complete  So you have a sentinel file   So the external programs looks for the sentinel file to exist  When it does it knows that it can safely use the data in the real file   Another way to do this  which is similar to how some browsers do it when downloading files  is to have the file named base-name part until the file is completely downloaded and then at the end rename the file to base-name  This way the external program can t  see  the file until it is complete  This way wouldn t require rewrite of the external program  Which might make it best for this situation

User · Answer

What s about Java code wrapped by a procedure  Simple and works fine   CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED SNOOZE AS public final class Snooze     private Snooze           public static void snooze Long milliseconds  throws InterruptedException         Thread sleep milliseconds          CREATE OR REPLACE PROCEDURE SNOOZE p Milliseconds IN NUMBER  AS     LANGUAGE JAVA NAME  Snooze snooze java lang Long

User · Answer

You can use the DBMS ALERT package as follows   CREATE OR REPLACE FUNCTION sleep seconds IN NUMBER  RETURN NUMBER AS     PRAGMA AUTONOMOUS TRANSACTION      message VARCHAR2 200       status  INTEGER  BEGIN     DBMS ALERT WAITONE  noname   message  status  seconds       ROLLBACK      RETURN seconds  END    SELECT sleep 3  FROM dual

User · Answer

Seems the java procedure function could work  But why don t you compile your function under a user like the application schema or a admin account that has this grant and just grant your developer account execute on it  That way the definer rights are used

User · Answer

From Oracle 18c you could use DBMS SESSION SLEEP procedure   This procedure suspends the session for a specified period of time  DBMS SESSION SLEEP  seconds  IN NUMBER    DBMS SESSION sleep is available to all sessions with no additional grants needed  Please note that DBMS LOCK sleep is deprecated  If you need simple query sleep you could use WITH FUNCTION  WITH FUNCTION my sleep i NUMBER  RETURN NUMBER BEGIN     DBMS SESSION sleep i       RETURN i  END  SELECT my sleep 3  FROM dual

User · Answer

Create a procedure which just does your lock and install it into a different user  who is  trusted  with dbms lock   USERA    grant USERA access to dbms lock    Then just grant USERB access to this function  They then wont need to be able to access DBMS LOCK    make sure you don t have usera and userb in your system before running this    Connect as a user with grant privs for dbms lock  and can create users  drop user usera cascade  drop user userb cascade  create user usera default tablespace users identified by abc123  grant create session to usera  grant resource to usera  grant execute on dbms lock to usera   create user userb default tablespace users identified by abc123  grant create session to userb  grant resource to useb  connect usera abc123   create or replace function usera f sleep  in time number   return number is begin  dbms lock sleep in time    return 1  end     grant execute on usera f sleep to userb   connect userb abc123      About to sleep as userb    select usera f sleep 5  from dual     Finished sleeping as userb        Attempt to access dbms lock as userb   Should fail     begin   dbms lock sleep 5   end        Finished

User · Answer

Short of granting access to DBMS LOCK sleep  this will work but it s a horrible hack   IN TIME INT  --num seconds v now DATE   -- 1  Get the date  amp  time  SELECT SYSDATE    INTO v now   FROM DUAL   -- 2  Loop until the original timestamp plus the amount of seconds  lt   current date LOOP   EXIT WHEN v now    IN TIME    1 86400    lt   SYSDATE  END LOOP

User · Answer

If Java is installed on your 11G then you can do it in a java class and call it from your PL SQL  but I am not sure that it does not require also a specific grant to call java

[sql] Sleep function in ORACLE

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle11g