Home

Description

This rule flags any use of the PL/SQL procedure DBMS_LOCK.SLEEP in application code. This procedure was used to pause execution for a specified duration in seconds.

DBMS_LOCK.SLEEP has been deprecated starting with Oracle 18c. Oracle recommends avoiding artificial wait times and instead designing logic around asynchronous processing or event-driven mechanisms.

Where delays are still needed (such as in testing or retry logic), DBMS_SESSION.SLEEP is suggested as an alternative. However, reconsidering the necessity of sleep logic entirely is encouraged for performance and scalability.

Key Benefits

• Future Compatibility: Ensures code does not depend on deprecated routines that may be removed in future Oracle releases.
• Efficiency: Encourages designs that avoid busy-waiting or unnecessary delays, leading to better CPU and resource utilization.
• Modernization: Promotes architectural patterns based on asynchronous and event-driven processing.
• Maintainability: Reduces technical debt by removing outdated or discouraged coding practices.

Non-compliant Code Example

BEGIN
    -- Pause execution for 5 seconds
    DBMS_LOCK.SLEEP(5);       --Non compliant code
    DBMS_OUTPUT.PUT_LINE('Resumed after 5 seconds.');
END;
/

Compliant Code Example

BEGIN
    -- Pause execution for 5 seconds using DBMS_SESSION
    DBMS_SESSION.SLEEP(5);    --Compliant code
    DBMS_OUTPUT.PUT_LINE('Resumed after 5 seconds.');
END;
/