6

Running a “background” job in PL/SQL

 3 years ago
source link: https://mikesmithers.wordpress.com/2020/01/20/running-a-background-job-in-pl-sql/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Running a “background” job in PL/SQL

Posted on January 20, 2020

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…

teddy_as_boris.jpg?w=584

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

teddy_vs_boris.jpg?w=584&h=779

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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK