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

Adding a delay or timer function in Oracle APEX using PL/SQL can be useful in various scenarios, such as simulating processing time, pacing background jobs, or controlling the flow of certain operations. One common requirement is to implement a simple 5-second pause in your PL/SQL code. This can be achieved efficiently using built-in PL/SQL features, enabling you to introduce timed waits without impacting overall application performance.

In Oracle APEX, adding a 5-second timer function using PL/SQL can be done primarily with the built-in DBMS_LOCK.SLEEP procedure. This procedure pauses the execution of your PL/SQL code for a specified number of seconds, allowing you to introduce a delay or wait period as part of your application logic.

Steps to Add a 5-Second Timer Using PL/SQL in Oracle APEX

1. Identify where to add the timer:
   The timer can be added inside any PL/SQL block, such as a process, a computation, a validation, or a dynamic action that executes PL/SQL code.

2. Use the DBMS_LOCK.SLEEP procedure:
   The syntax is simple:

   DBMS_LOCK.SLEEP(seconds => 5);
   

   This command tells Oracle to pause the execution for 5 seconds.

3. Example of adding a 5-second delay in a Process:

   • Go to your APEX application and open the Page Designer.

   • Under the “Processing” section, create a new Process or edit an existing one where you want to add the delay.

   • In the PL/SQL Code section, add the following code snippet:

   BEGIN
      -- Your logic before the delay
      DBMS_LOCK.SLEEP(5); -- Pause for 5 seconds
      -- Your logic after the delay
   END;
   

   This will cause the process to wait 5 seconds before continuing.

4. Using the Timer in Dynamic Actions:
   You can also create a Dynamic Action that executes PL/SQL code and includes the sleep timer to control the timing of actions on the page.

5. Considerations:

   • Avoid using unnecessary delays in UI-interactive code to maintain a responsive user experience.

   • Using DBMS_LOCK.SLEEP in server-side processes is best suited for scenarios such as throttling background jobs or simulating wait times during development.

   • Ensure the user is aware if a delay affects the UI, possibly using loading indicators.

By using the DBMS_LOCK.SLEEP function, you can precisely control execution timing in Oracle APEX, making it easy to add pauses like a 5-second timer in your PL/SQL code blocks.

Example

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;

Incorporating a 5-second timer in your PL/SQL code within Oracle APEX is straightforward and can enhance your application's control flow where timing is essential. By using simple techniques like DBMS_LOCK.SLEEP, you can add precise delays that help manage processing or user interactions smoothly. This approach keeps your application responsive while meeting timing requirements effectively.