Introduction
Adding a timer function in PL/SQL can be useful for controlling execution flow, simulating delays, or pacing processes within Oracle APEX applications or database procedures. Implementing a simple 5-second delay allows you to pause execution for a specified time, which can be helpful during testing, throttling resource usage, or synchronizing tasks. Using built-in Oracle functions, you can create an efficient timer without complex coding.
To add a 5-second timer function in PL/SQL, you can use the built-in DBMS_LOCK.SLEEP procedure. This procedure pauses the execution of your PL/SQL code for a specified number of seconds. It’s a simple and effective way to introduce delays or pauses in your Oracle APEX application processes or database routines.
Here’s how to do it in detail:
Step 1: Use DBMS_LOCK.SLEEP
The DBMS_LOCK.SLEEP procedure accepts a single parameter representing the number of seconds to pause execution. To create a 5-second delay, you simply call:
BEGIN
DBMS_LOCK.SLEEP(5);
END;
This block causes the session to sleep for 5 seconds before continuing with the next statements.
Step 2: Integrate into Your PL/SQL Code
You can include this sleep call inside any PL/SQL block, procedure, or anonymous block within Oracle APEX, such as:
-
Processes on page submit
-
Dynamic actions invoking PL/SQL code
-
Scheduled jobs or background tasks
Example in a page process:
BEGIN
-- Your pre-delay logic here
DBMS_LOCK.SLEEP(5);
-- Your post-delay logic here
END;Step 3: Considerations
-
The sleep affects only the current session; other database sessions remain unaffected.
-
Use this carefully in production environments because it holds session resources during the sleep.
-
Avoid using long sleep durations that can cause unnecessary waits or performance issues.
For longer or more complex timing, consider other scheduling or asynchronous methods.
Step 4: Testing
-
Add the PL/SQL block with the sleep call to a page process or button action.
-
Run the page and observe the delay before subsequent processing completes.
Use timing tools or logs to verify the pause duration.
Adding a 5-second timer in PL/SQL using DBMS_LOCK.SLEEP is a straightforward method to introduce execution delays in Oracle APEX applications or database procedures. This simple function helps control timing in workflows, testing scenarios, or controlled pauses, enhancing your ability to manage process flow effectively.
Adding a 5-Second PL/SQL Timer Function in Oracle APEX
A timer function in APEX can be useful for delaying actions such as refreshing reports, executing processes, or redirecting users. Since PL/SQL executes on the server, implementing a 5-second delay requires using DBMS_LOCK.SLEEP or DBMS_SESSION.SLEEP.
Method 1: Using DBMS_LOCK.SLEEP in a PL/SQL Process
The DBMS_LOCK.SLEEP procedure pauses execution for a given number of seconds.
Steps to Implement
Create a PL/SQL Process
In Page Designer, go to Processing > Click Create Process
Set Execution Point to After Submit
Set Language to PL/SQL
Enter the following code:
BEGIN
DBMS_LOCK.SLEEP(5); -- Wait for 5 seconds
END;
Save and Test
Submit the page and observe the 5-second delay before the next action executes.
Method 2: Using JavaScript to Delay a Dynamic Action
If you want the delay to occur without blocking other processes, use JavaScript instead of PL/SQL.
Steps to Implement
Create a Dynamic Action
Event: Click (on a button)
Action: Execute JavaScript Code
Code:
setTimeout(function() {
apex.submit("SAVE"); // Simulate form submission after 5 seconds
}, 5000);
Save and Test
When the button is clicked, APEX will wait 5 seconds before submitting.
Method 3: Using DBMS_SESSION.SLEEP in a PL/SQL Block
DBMS_SESSION.SLEEP is another alternative to DBMS_LOCK.SLEEP but works in anonymous PL/SQL blocks.
Example Usage
BEGIN
DBMS_SESSION.SLEEP(5); -- Wait for 5 seconds
END;
This can be placed in a Before Header Process if you need to delay page processing.
Best Practices
Avoid using PL/SQL sleep functions excessively, as they can block database resources.
Use JavaScript for UI-related delays, since it does not block the session.
Ensure long delays are necessary, as they can impact user experience.
Adding a 5-second timer in APEX can be done using PL/SQL (DBMS_LOCK.SLEEP, DBMS_SESSION.SLEEP) or JavaScript (setTimeout) depending on whether the delay is needed for server-side or client-side processing.
EXAMPLE:
Create or replace function "SLEEP5SECS" return number as
l_now timestamp := systimestamp;
l_end_time timestamp;
begin
l_end_time := l_now + numtodsinterval (5, 'second');
while(l_end_time > l_now) loop
l_now := systimestamp;
end loop;
return 0;
end "SLEEP5SECS";
/
Call it from a PL/SQL Source
declare
lv_resultado number :=0;
begin
lv_resultado := SLEEP5SECS();
htp.p(lv_resultado);
end;
Conclusion
Implementing a 5-second timer in PL/SQL is straightforward and valuable for managing execution timing in your Oracle APEX applications or database logic. By using Oracle’s DBMS_LOCK.SLEEP procedure, you can pause processing safely and predictably, giving you control over delays without impacting system stability. This technique enhances your ability to build responsive, well-timed workflows.
