codemonth.dk

One project every month - making stuff better ...

T minus 10 ... 9 ... 8 ... 7

While creating the lightweight monitoring package, I could see that I could use the same technique for another little project I wanted to create. A fast instance wide countdown, that was not dependent on a table or a sequence. Since I already had the basic framework ready, it was pretty fast to implement.

The code is already done, and ready to download on my github repository page and the installation instructions are on that page as well.

Once you have the package installed, this is how you can use it:

Contents of test.sql


/* SESSION 1 */

REM Setup a a countdown ticker. When the ticker reaches zero
REM it will execute a procedure called cd_test_proc

exec countdown_ninja.init('cd_test', 5, 'cd_test_proc');

REM Decrement using the function
select countdown_ninja.cd('cd_test') from dual;

REM Open a new session and run the same statement
/* SESSION 2 */

REM Decrement using the function
select countdown_ninja.cd('cd_test') from dual;

REM Back in session 1, decrement by the procedure
/* SESSION 1 */

exec countdown_ninja.cd('cd_test');

set serveroutput on

declare
  x number;
begin
  -- Using procedure
  countdown_ninja.cd('cd_test');
  -- Using function
  x := countdown_ninja.cd('cd_test');
  -- Output the result
  dbms_output.put_line('Countdown has reached: ' || to_char(x));
end;
/

Result of running test.sql in sqlplus


SQL> @test

PL/SQL procedure successfully completed.


COUNTDOWN_NINJA.CD('CD_TEST')
-----------------------------
			    4


COUNTDOWN_NINJA.CD('CD_TEST')
-----------------------------
			    3


PL/SQL procedure successfully completed.

Countdown has reached: 0

PL/SQL procedure successfully completed.

SQL>

As the comments in test.sql says, when you init the countdown ticker, you can specify a procedure to call, once the countdown reaches zero. It is the final argument in the init procedure. For now, nothing is passed to the procedure. In the future, I will add the ticker name as one of the automatic input parameters and maybe a couple of others as well

Tagged in : DBMS_SESSION, sys_context