plsql sleep without using dbms_lock.sleep/DBMS_SESSION.sleep

user_10485021 0
Viewed 2197

Has anyone have any oracle sql that will let the program wait for 10 seconds without using dbms_lock.sleep/DBMS_SESSION.sleep functions.

In UAT instance, i want run the update statement every 10s and my current db role does not have privilages to use dbms_lock.sleep/DBMS_SESSION.sleep

plsql oracle
2 Answers

One of the alternative i could think of is use of the method sleep from the Java class Thread, which you can easily use through providing a simple PL/SQL wrapper procedure as shown below:

Procedure:

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

Execution

BEGIN
    DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    SLEEP(5 * 1000); -- Resting for 5 sec
    DBMS_OUTPUT.PUT_LINE('End   ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/

Output:

Start 2020-03-25 12:57:24
End   2020-03-25 12:57:36
user_65295370

I had the same problem and wrote this code to approximate a wait function using only PL/SQL:

DECLARE

    v_minimum_seconds_to_wait   NUMBER := 10; /* this is the only value you need to edit */
    v_time_to_output            DATE;
    
    PROCEDURE wait_at_least(
        p_minimum_seconds_to_wait   IN NUMBER
    )
    IS
    
        v_result        VARCHAR2(5) := 'TRUE';
        v_target_time   DATE :=  SYSDATE + (p_minimum_seconds_to_wait / 86400); /* convert incoming number to seconds */
    
        FUNCTION is_it_after(
            p_target_time   IN DATE
        )
        RETURN VARCHAR2
        IS
        
            v_result_after  VARCHAR2(5) := 'TRUE';
        
        BEGIN
        
            IF SYSDATE < p_target_time THEN
                v_result_after := 'FALSE';
            END IF;
            
            RETURN v_result_after;
            
        END is_it_after;
    
    BEGIN
    
        v_result := is_it_after(v_target_time);
    
        WHILE v_result != 'TRUE' LOOP
            v_result := is_it_after(v_target_time);
        END LOOP;
        
    END wait_at_least;
    
BEGIN

    v_time_to_output := SYSDATE;
    DBMS_Output.put_line('Starting time: '|| TO_CHAR(v_time_to_output, 'DD-MON-YYYY HH24:MI:SS'));
    
    wait_at_least(
        p_minimum_seconds_to_wait => v_minimum_seconds_to_wait
    );

    v_time_to_output := SYSDATE;
    DBMS_Output.put_line('Ending time: '|| TO_CHAR(v_time_to_output, 'DD-MON-YYYY HH24:MI:SS'));

END;

This is tested against Oracle 11g:

Starting time: 25-AUG-2022 17:09:01

Ending time: 25-AUG-2022 17:09:11

user_14309960
Related