Evie Summers
The SLEEP function in PL/SQL is used to suspend a session for a specified duration, typically in seconds or milliseconds. While Oracle Database 18c introduced the DBMS_SESSION.SLEEP function, accessible to all without additional privileges, earlier versions used DBMS_LOCK.SLEEP, which required explicit grants. The sleep function is valuable for introducing delays in stored procedures or queries, but its misuse can impact database performance. When using the sleep function, it's crucial to consider the potential trade-offs between introducing delays and maintaining efficient database operations.
| Characteristics | Values |
|---|---|
| Function | DBMS_SESSION.SLEEP |
| Previous Function | DBMS_LOCK.SLEEP |
| Functionality | Holds/suspends the session for a particular/defined number of seconds |
| Parameters | Accepts any valid number, including fractions down to hundredths of a second |
| Oracle Version | Introduced in Oracle 18c |
| Previous Versions | Call Java from PL/SQL for sleeps in milliseconds or less |
Explore related products
What You'll Learn
Using Java from PL/SQL for sleeps in milliseconds
If you want to introduce sleeps of milliseconds (or less) into your PL/SQL, you can call Java from PL/SQL. For example, you can use the "sleep" method from the Java class "Thread". Note that "Thread.sleep" uses milliseconds. Here is an example of how to use it:
Sql
CREATE OR REPLACE PROCEDURE SLEEP (P_MILLI_SECONDS IN NUMBER) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
You can then use the procedure as follows:
Sql
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;
This will pause the execution of your PL/SQL code for 5 seconds.
Another option is to use the DBMS_LOCK.SLEEP() function, which is available in Oracle Database 18c and earlier. However, this function is part of the DBMS_LOCK package, which is not granted to PUBLIC by default. Starting in Oracle Database 18c, the sleep function is also available in the DBMS_SESSION package, which is granted to PUBLIC by default. The function code in DBMS_SESSION.SLEEP is identical to DBMS_LOCK.SLEEP, so you can use either one. Here is an example of how to use it:
Sql
DECLARE
V_start TIMESTAMP;
V_end TIMESTAMP;
BEGIN
V_start := SYSTIMESTAMP;
!-- Sleep for 10 seconds
DBMS_SESSION.SLEEP(10);
V_end := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE('This procedure started at ' || v_start);
DBMS_OUTPUT.PUT_LINE('This procedure ended at ' || v_end);
END;
This will suspend the session for 10 seconds and then output the start and end times of the procedure.
Unlock Laneige Cica Sleeping Mask's Power
You may want to see also
Explore related products
DBMS_SESSION.SLEEP in Oracle Database 18c
The SLEEP procedure is added to the DBMS_SESSION package and deprecated from the DBMS_LOCK package in Oracle Database 18c. The DBMS_SESSION package is available to all sessions with no additional grants needed and no dependency on the DBMS_LOCK package. The procedure suspends the session for a specified number of seconds. The seconds parameter can accept any valid number, including fractions down to hundredths of a second.
The DBMS_LOCK package was not accessible by default, so an explicit grant was necessary for a session to use it. By giving access to this package, you also give the grantee the ability to execute the other subroutines in the DBMS_LOCK package. The DBMS_SESSION.SLEEP function in Oracle Database 18c has identical functionality to DBMS_LOCK.SLEEP. The code in DBMS_SESSION.SLEEP is identical to DBMS_LOCK.SLEEP, so you can do a simple find and replace in your code.
Sql
DECLARE v_start TIMESTAMP;
V_end TIMESTAMP;
BEGIN
V_start := SYSTIMESTAMP;
- Sleep for 10 seconds
DBMS_SESSION.SLEEP(10);
V_end := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
DBMS_OUTPUT.PUT_LINE('This procedure ended at ' ||v_end);
END;
The output of the above code will be:
This procedure started at 10-SEP-22 12.39.40.587041 AM
This procedure ended at 10-SEP-22 12.39.50.637738 AM
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.02
Note that the granularity of the DBMS_SESSION.SLEEP function is 1/100th of a second. If you want to introduce sleeps of milliseconds (or less) into your PL/SQL, you can call Java from PL/SQL.
Bonnet Care: Tips for a Comfortable Sleep
You may want to see also
Explore related products
DBMS_LOCK.SLEEP function
The `DBMS_LOCK.SLEEP` function in PL/SQL is used to suspend a session for a specified number of seconds. It is part of the DBMS_LOCK package, which is not granted to the public by default due to other powerful functions in the package. As a result, users previously had to request access from the DBA or security team to use the `SLEEP` function.
Sql
DECLARE
V_start TIMESTAMP;
V_end TIMESTAMP;
BEGIN
V_start := SYSTIMESTAMP;
- Sleep for 10 seconds
DBMS_LOCK.SLEEP(10);
V_end := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE('This procedure started at ' || v_start);
DBMS_OUTPUT.PUT_LINE('This procedure ended at ' || v_end);
END;
In the above code, the `SLEEP` function is used to pause the execution of the procedure for 10 seconds. The timestamps at the beginning and end of the procedure are stored in the `v_start` and `v_end` variables, respectively, and then printed using `DBMS_OUTPUT.PUT_LINE` to show the duration of the sleep.
However, starting with Oracle Database 18c, the `SLEEP` function was moved from the `DBMS_LOCK` package to the `DBMS_SESSION` package, which is accessible to all sessions by default without any additional grants needed. The function code in `DBMS_SESSION.SLEEP` is identical to `DBMS_LOCK.SLEEP`, so you can simply replace the package name in your code.
It is worth noting that if you require sleeps in milliseconds or less, you can call Java from PL/SQL using the "Thread.sleep" method from the "Thread" Java class.
Sleep Apnea and CPAP: America's Hidden Epidemic
You may want to see also
Explore related products
$17.87 $19.99
SLEEP procedure in the DBMS_SESSION package
Oracle has long allowed users to add a sleep command to stored procedures, suspending a session for a specified number of seconds. The sleep function was originally part of the DBMS_LOCK package, which was not accessible by default, requiring explicit permission from the DBA or security team.
However, starting with Oracle Database 18c, the sleep function is now available in the DBMS_SESSION package, which is accessible to the public by default. This means that users can call the function without any additional privileges. The function code in DBMS_SESSION.SLEEP is identical to DBMS_LOCK.SLEEP, so users can simply find and replace in their code.
The SLEEP procedure is added to the DBMS_SESSION package and deprecated from the DBMS_LOCK package in Oracle Database 18c. It is available to all sessions with no additional grants needed and no dependency on the DBMS_LOCK package. The procedure suspends the session for the specified number of seconds, and the seconds parameter can accept any valid number, including fractions down to hundredths of a second.
DECLARE
V_start TIMESTAMP;
V_end TIMESTAMP;
BEGIN
V_start := SYSTIMESTAMP;
- Sleep for 10 seconds
DBMS_SESSION.SLEEP(10);
V_end := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
DBMS_OUTPUT.PUT_LINE('This procedure ended at ' ||v_end);
END;
The Ultimate Boppy Pillow Guide for Better Sleep
You may want to see also
Explore related products
$9.87 $15.99
APEX_UTIL.PAUSE function
The APEX_UTIL.PAUSE function is a procedure that can be used to introduce a delay or pause in a PL/SQL program. This function is available in the APEX_UTIL package, which is publicly accessible if APEX is installed.
To use the APEX_UTIL.PAUSE function, you need to specify the duration of the pause in seconds. For example, to pause the program execution for 5 seconds, you can call the function as follows:
Sql
APEX_UTIL.PAUSE(5);
This function is particularly useful when you need to ensure that a certain amount of time has passed before continuing with the next steps in your program. It can be helpful in scenarios where you are working with external systems or performing time-dependent operations.
It's important to note that the APEX_UTIL.PAUSE function is not the only way to achieve a delay in PL/SQL. There are alternative approaches, such as using the DBMS_LOCK.SLEEP procedure or creating a custom Java class and invoking it from your PL/SQL code. However, the APEX_UTIL.PAUSE function provides a straightforward and accessible option, especially if you already have APEX installed in your environment.
When using the APEX_UTIL.PAUSE function, it's crucial to consider the potential impact on the overall performance of your application. Introducing deliberate pauses in your code can affect the responsiveness and efficiency of your program. Therefore, it's recommended to use this function judiciously and only when necessary. Additionally, always test the behavior of your program with pauses to ensure it aligns with your expected outcomes.
Dr Teal's Sleep Lotion: Application and Benefits
You may want to see also
Frequently asked questions
You can add a sleep command to your stored procedures to suspend a session for a specified number of seconds. Starting with Oracle Database 18c, you can use the DBMS_SESSION.SLEEP function, which is available to all sessions by default. The code would look like this:
```sql
DECLARE v_start TIMESTAMP;
v_end TIMESTAMP;
BEGIN
v_start := SYSTIMESTAMP;
-- Sleep for 10 seconds
DBMS_SESSION.SLEEP(10);
v_end := SYSTIMESTAMP;
DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
DBMS_OUTPUT.PUT_LINE('This procedure ended at ' ||v_end);
END;
```
One alternative is to use the DBMS_LOCK.SLEEP function. However, this function is not granted to PUBLIC by default, and you would need additional privileges to use it. Another option is to call Java from PL/SQL if you want to introduce sleeps of milliseconds or less.
You can use the "sleep" method from the Java class "Thread" by providing a simple PL/SQL wrapper procedure. Here is an example:
```sql
CREATE OR REPLACE PROCEDURE SLEEP (P_MILLI_SECONDS IN NUMBER) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
```
