plsql sleep without using dbms_lock.sleep/DBMS_SESSION.sleep

Question

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

Looks like you are operating in a properly tightly controlled UAT, I guess I'm going to raise sheer horror for developers these days: Go talk to your DBA and get the necessary privilege. Be prepared to answer: 1. What you are trying to accomplish? Why this method is best? Be ready to accept alternatives. 2. Why you did not preform this in TEST environment? Or be able to show the those results. 3. How long will you need the privilege? 4. Will the wait process be required in PROD? — Belayer, Mar 25 '20 at 01:36
Are you sure it's a privilege issue and not a version issue? DBMS_SESSION is granted to PUBLIC and should work for all users. But the procedure DBMS_SESSION.SLEEP is new to 18c. Check V$VERSION on all your databases. — Jon Heller, Mar 25 '20 at 03:37
If you're below 18c and your DBA won't grant access to `dbms_lock`, they might be willing to create [a wrapper procedure](https://stackoverflow.com/a/2564566/266304) for you instead. The Java method is probably easier though (and I've used that successfully [before](https://stackoverflow.com/a/29166086/266304)!). — Alex Poole, Mar 25 '20 at 09:11
@JonHeller. We are still using 12c. Thanks for the information. — BK Barathi, Mar 25 '20 at 16:02

score 4 · Answer 1 · answered Mar 25 '20 at 07:30

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

might be problematic to create if admins are strict with grants — downvoteit, Mar 27 '20 at 15:11

score 0 · Answer 2 · answered Aug 25 '22 at 21:09

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

plsql sleep without using dbms_lock.sleep/DBMS_SESSION.sleep

2 Answers2