How do I ADD a 5 second PL/SQL timer function

 

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

1. 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;

2. 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

1. 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);

2. 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.