Running a “background” job in PL/SQL

Teddy has observed the recent General Election campaign with some interest and has concluded that he has what it takes to be the next Prime Minister.

It’s not just the haircut, which does now look extremely Prime Ministerial…

Politics is a robust business but Teddy’s more than capable of playing “ruff” :

He firmly believes in the need to streamline Government at Cabinet level, which has the incumbent Chief Mouser to the Cabinet Office a little nervous.
He’s also well used to being followed around by a “pooper scooper”. And not to put too fine a point on it, there’s more than one reason that he’s known as a “shaggy” dog.

If he’s going to make it in politics, Teddy knows that he doesn’t have time to waste waiting for that pesky long-running job he’s just started. Oh no, he needs to use his evenings to get on with building his power base.
Fortunately, Oracle facilitates detached execution of PL/SQL blocks by means of the DBMS_SCHEDULER package. Now, I know what you’re thinking, that’s going to involve a lot of fiddly setup with schedules and windows and chains and stuff. Well, you may be pleasantly surprised…

The package

Teddy has a packaged procedure that he needs to test :

create or replace package long_runner as
    procedure write_log( 
        i_msg_type in logs.message_type%type,
        i_msg logs.message%type);
        
    procedure marathon( i_sleep in pls_integer, i_string in varchar2);
end long_runner;
/
    
create or replace package body long_runner as
    procedure write_log( 
        i_msg_type in logs.message_type%type,
        i_msg logs.message%type)
    is
    pragma autonomous_transaction;
    begin
        insert into logs( message_type, message)
        values( i_msg_type, i_msg);
        
        commit;
    end write_log;    
        
    procedure marathon( i_sleep in pls_integer, i_string in varchar2)
    is
        l_msg logs.message%type;
    begin
        write_log('INFO', 'i_sleep : '||i_sleep);
        write_log('INFO', 'i_string : '||i_string);
        for i in 1..26 loop
            l_msg := null;
            for j in 1..i loop
                l_msg := l_msg||'z';
            end loop;
            write_log('INFO', initcap( l_msg));
            dbms_session.sleep( i_sleep);
        end loop;
        write_log('INFO', 'Completed');
    end marathon;    
end long_runner;
/

Normally, he’d kick it off in a simple PL/SQL block :

begin
    long_runner.marathon( 30, 'Teddy for PM!');
end;
/

Unfortunately, it’s almost time to leave for the day and he doesn’t trust Windows not to do an update and reboot itself.

Fortunately, Teddy’s not above a (quick and) dirty trick…

declare
    stmnt_block varchar2(4000);
begin
    stmnt_block := q'[
        begin
            long_runner.marathon( 30, 'Teddy for PM!');
        end;
        ]';
        
    dbms_scheduler.create_job(
        job_name => 'checkit_tomorrow',
        job_type => 'PLSQL_BLOCK',
        job_action => stmnt_block,
        start_date => sysdate, -- run now
        enabled => true,
        auto_drop => true,
        comments => 'My background job - because I have a life');
end;
/

He’s created a DBMS_SCHEDULER job on-the-fly. The job is set to run immediately by setting the start_date to now. The job will be dropped once it’s completed ( auto_drop set to true).

Run this and it returns almost immediately. That’s because the job has been submitted rather than the PL/SQL block having finished.
If we want to check progress, we can take a look at the scheduler views…

Tracking job progress

When the job starts, we can see it in USER_SCHEDULER_JOBS :

select to_char(start_date, 'DD-MON-YYYY HH24:MI') as start_date, 
    state
from user_scheduler_jobs
where job_name = 'CHECKIT_TOMORROW'
/

START_DATE                 STATE               
-------------------------- --------------------
20-JAN-2020 19:45          RUNNING             

If, for any reason, Teddy needs to terminate the job, he can simply run :

exec dbms_scheduler.stop_job('CHECKIT_TOMORROW', true);

As noted previously, the job will drop itself on completion at which point it will disappear from these views.

To see the outcome of the job :

select log_id, log_date, status
from user_scheduler_job_log
where job_name = 'CHECKIT_TOMORROW';

    LOG_ID LOG_DATE                         STATUS                        
---------- -------------------------------- ------------------------------
     53216 20-JAN-20 19.50.01.854589000 GMT SUCCEEDED                                          

Sometimes we need further details. For example, if the job failed, the error stack will be included in the
ERRORS column of the USER_SCHEDULER_JOB_RUN_DETAILS views :

select run_duration, status,
    error#, additional_info, errors
from user_scheduler_job_run_details
where job_name = 'CHECKIT_TOMORROW'
and log_id = 53216 --log id from user_scheduler_job_log;

RUN_DURATION        STATUS                             ERROR# ADDITIONAL_INFO      ERRORS              
------------------- ------------------------------ ---------- -------------------- --------------------
+00 00:05:00.000000 SUCCEEDED                               0                                     

In this case however, it looks like Teddy’s run was perfect…just like it will be in 2024.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.