DBMS_JOB and 19c – code changes needed

Sep 2021: Important update at tail of this post

Here’s a “gotcha” brought to my attention by one of our AskTOM readers. I’ve mentioned in the past that DBMS_JOB, having been deprecated in favour of DBMS_SCHEDULER, got a new lease of life in 19c because under the covers we translated calls to create a job under DBMS_JOB to the same mechanism in DBMS_SCHEDULER.

The benefit of that is that we don’t need to maintain our older DBMS_JOB code base, but your existing code is fine to keep running. However, as I said in the other post, you do need to alter your privileges, but here is another discovery that might impact you as well.

If I have a procedure that accepts a number of parameters, and that procedure was to be called (many times) from DBMS_JOB, or perhaps the parameters for that procedure contained sensitive information, then building a string to dispatch the DBMS_JOB could be problematic. A typical construction could be something like

l_job_string := 'begin reset_password('''||p_username||''','''||p_password||'''); end;'

to obtain a DBMS_JOB submission block of:

begin reset_password('SCOTT','TIGER'); end;

That works but could easily either expose sensitive information (for example, the password above) or smash your system with parsing costs for every call. And of course, if any of this information was being passed from somewhere upstream, then concatenation opens up the risks of SQL injection.

A common technique used in the past to avoid the SQL injection and parsing risks was that DBMS_JOB would make the running job number available to the PLSQL block that was being run. For example, lets say you wanted to pass 3 parameters to a DBMS_JOB procedure. Rather than have your procedure accept those 3 parameters directly, you would create a table to hold those parameters, with the job number as a primary key:

SQL> create table my_job_parameters
  2    (job_id number,
  3     param1 date,
  4     param2 varchar2(30),
  5     param3 int
  6  );

To pass those values to a DBMS_JOB-submitted procedure, you would create a job, take note of the job number and store it in the above table, along with the parameters that this invocation of the procedure should receive, eg

SQL> declare
  2    l_job number;
  3  begin
  4    dbms_job.submit( l_job, 'my_program(JOB);', sysdate);
  5    
  6    insert into my_job_parameters values (l_job,sysdate,'x',1);  <<=== the parameters for this job
  7    commit;
  8  end;
  9  /

Notice that the called routine “my_program” takes a parameter “JOB” which is passed automatically by the DBMS_JOB runtime. Thus that procedure would be coded to use the passed job parameter to discover the rest of the “true” parameters:

SQL> create or replace
  2  procedure my_program(p_job in int)
  3  is
  4  begin
  5    --
  6    -- select * into ... from my_job_parameters where job_id = p_job;
  7    --
  8    -- then processing continues now that we have the parameters
  9    --
 10  end;
 11  /

Thus in a nutshell,

• you submit a job,
• that returns a JOB number,
• you store additional parameters in a table keyed by that JOB number,
• the procedure (running as a job) picks up those details when launched by the (DBMS_JOB) scheduler.

In later releases, we didn’t even need to pass “JOB” because SYS_CONTEXT(‘USERENV’,’BG_JOB_ID’) could be used from within the procedure to pick up the currently running job number. However, in 19c we know that DBMS_JOB is transparently translated to the equivalent objects within DBMS_SCHEDULER. Because of this, you get a slightly different behaviour when it comes to accessing the job number. Here is a more complete version of the solution above, which also serves to demonstrate how 19c changes things.

First I’ll have a table to hold my job parameters. I’ll also create a table called CAPTURED_DETAILS so we can track some relevant details throughout the execution of our code.

SQL> create table my_job_parameters
  2    (job_id number primary key,
  3     param1 date,
  4     param2 varchar2(30),
  5     param3 int
  6  );

Table created.

SQL> create table captured_details(msg varchar2(200));

Table created.

Now here is my procedure in “old style” DBMS_JOB style. I’ll assume that the execution environment will be passing a JOB parameter. Line 6 shows what I would then typically do in my code. I would use the passed JOB to fetch the rest of the parameters and proceed. In this way, they never needed to be passed directly and were never exposed to the outside world via the anonymous block I’ll be passing via DBMS_JOB.

SQL> create or replace
  2  procedure my_program(p_job in int)
  3  is
  4  begin
  5    --
  6    -- select * into ... from my_job_parameters where job_id = p_job;
  7    --
  8    -- then processing continues
  9    --
 10    insert into captured_details values ('p_job='||p_job);
 11    insert into captured_details values ('BG_JOB_ID='||sys_context('USERENV','BG_JOB_ID'));
 12     
 13    commit;
 14    --
 15    -- just so you can look at dba_jobs_running / dba_scheduler_running_Jobs if you like
 16    --
 17    dbms_session.sleep(60);
 18  end;
 19  /

Procedure created.

So now I create my job to obtain the job number. Then I can use the job to store the rest of the parameters in the MY_JOB_PARAMETERS table. You can see the benefit on line 4, where the anonymous block is static and exposes no parameters. I commit the block to set the job in motion asynchronously.

SQL> declare
  2    l_job number;
  3  begin
  4    dbms_job.submit( l_job, 'my_program(JOB);', sysdate);
  5    insert into captured_details values ('DBMS_JOB returned job='||l_job);
  6    insert into my_job_parameters values (l_job,sysdate,'x',1);
  7    commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

But in 19c, we need to look at what is revealed from our CAPTURED_DETAILS table.

SQL>
SQL> select * from captured_details;

MSG
--------------------------------------------------------------------------------
DBMS_JOB returned job=110  
p_job=99469                
BG_JOB_ID=99469

The job number that comes out of the DBMS_JOB call is 110, which is a sequence related purely to DBMS_JOB. However, when we executed the routine from the scheduler (which is no longer DBMS_JOB but DBMS_SCHEDULER) the job number that is passed in is not 110 but 99469. We’ve lost the link between our running job and the parameters table because the job number seen within the job no longer matches the job number we returned from the DBMS_JOB call.

So where does 99469 come from? That ID is now the object# of the scheduler job we created. A job created via DBMS_SCHEDULER is indeed a fully fledged database object.

SQL> select * from user_objects
  2  where object_type = 'JOB'
  3  @pr
==============================
OBJECT_NAME                   : DBMS_JOB$_110
SUBOBJECT_NAME                :
OBJECT_ID                     : 99469
DATA_OBJECT_ID                :
...

If you have been using this technique to pass parameters, you can work around this easily in code. Use the job number that the scheduler passed in (the object ID) to determine the original job sequence number and then strip off the leading part of the object name.

SQL> create or replace
  2  procedure my_program(p_job in int) is
  3    l_job int;
  4  begin
  5    select replace(object_name,'DBMS_JOB$_')
  6    into   l_job
  7    from   user_objects
  8    where  object_id  = p_job;
  9    --
 10    -- select * into ... from my_job_parameters where job_id = l_job;   <<<=== NOT p_job
 11    --
 12    -- then processing continues
 13    --

This is an easy fix, but if you are changing the code anyway, then (unless you need the transactional capabilities of DBMS_JOB) it is probably time to refactor the code to use DBMS_SCHEDULER directly.

September 2021: Note that this bug has been in fixed in version 19.12 of the database so that it now mimics the original DBMS_JOB behaviour. You no longer need this workaround from 19.12 onwards.