Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

DBMS_SCHEDULER Job based on chain - set parameters

Gareth SSep 8 2022

Hi,
I am sure that this has been discussed before and, I think, I am missing something/not understanding something correctly. I've looked at this thread, but cannot find the threads it seems to reference. This is being done using Oracle 19c.
The idea is that when a file arrives I need to kick off a number of steps that will decrypt the file (arrives GPG/PGP encrypted) using an external job which needs the filename passing in as a parameter. Once that is complete other jobs will run to update the location for an external table and then load a specific table depending on the file name pattern. I have defined the programs as parameterised STORED_PROCEDURE's and individually they all work.
If it helps this is what I have so far (I've left out the program definitions, but each would take some sort of parameter that needs defining at run time)

DECLARE
  c_chain_name CONSTANT VARCHAR2(128) := 'LOAD_CHAIN';
  c_decrypt_prog CONSTANT VARCHAR2(128) := 'DECRYPT_PROG';
  c_update_ext_prog CONSTANT VARCHAR2(128) := 'UPDATE_EXT_LOC_PROG';
  c_load_prog_name CONSTANT VARCHAR2(128) := 'LOAD_PROG';
  c_rm_prog_name CONSTANT VARCHAR2(128) := 'RM_PROG';
 
  -- Variables
  v_ind PLS_INTEGER;
BEGIN
  dbms_scheduler.create_chain(
  chain_name => c_chain_name,
  comments => 'The chain to process a file into a table');
 
  dbms_scheduler.define_chain_step(
  chain_name => c_chain_name,
  step_name => 'DECRYPT_FILE',
  program_name => c_decrypt_prog);
 
  dbms_scheduler.define_chain_step(
  chain_name => c_chain_name,
  step_name => 'UPDATE_EXT_LOCATION',
  program_name => c_update_ext_prog);
 
  dbms_scheduler.define_chain_step(
  chain_name => c_chain_name,
  step_name => 'LOAD_DATA',
  program_name => c_load_prog_name);
 
  dbms_scheduler.define_chain_step(
  chain_name => c_chain_name,
  step_name => 'REMOVE_DATA_FILE',
  program_name => c_rm_prog_name);
 
  dbms_scheduler.define_chain_step(
  chain_name => c_chain_name,
  step_name => 'REMOVE_GPG_FILE',
  program_name => c_rm_prog_name);

  -- Now define the dependancies
  dbms_scheduler.define_chain_rule(
  chain_name => c_chain_name,
  condition => 'TRUE',
  action => 'START "DECRYPT_FILE"');
 
  dbms_scheduler.define_chain_rule(
  chain_name => c_chain_name,
  condition => '"DECRYPT_FILE" COMPLETED',
  action => 'START "UPDATE_EXT_LOCATION"');
 
  dbms_scheduler.define_chain_rule(
  chain_name => c_chain_name,
  condition => '"UPDATE_EXT_LOCATION" COMPLETED',
  action => 'START "LOAD_DATA"');
 
  dbms_scheduler.define_chain_rule(
  chain_name => c_chain_name,
  condition => '"LOAD_DATA" COMPLETED',
  action => 'START "REMOVE_DATA_FILE"');
 
  dbms_scheduler.define_chain_rule(
  chain_name => c_chain_name,
  condition => '"LOAD_DATA" COMPLETED',
  action => 'START "REMOVE_GPG_FILE"');
 
  dbms_scheduler.define_chain_rule(
  chain_name => c_chain_name,
  condition => '"REMOVE_DATA_FILE" COMPLETED AND "REMOVE_GPG_FILE" COMPLETED',
  action => 'END');
END;
/

This gives me:
image.pngThe "problem" (my lack of understanding) comes when I try to run a job based on the chain. More than one file can come in at a time and I want them to load in parallel.
I understand that you cannot modify the parameters whilst the job chain is running, and I could live with that limitation as, once the file has arrived all the parameters could be derived prior to enabling the job.
So, what I had hoped to do was something like:

DECLARE
  c_chain_name CONSTANT VARCHAR2(30) := 'LOAD_CHAIN';
  c_job_name CONSTANT VARCHAR2(128) := DBMS_SCHEDULER.generate_job_name;
BEGIN
  dbms_scheduler.create_job(
    job_name => c_job_name,
    job_type => 'CHAIN',
    job_action => c_chain_name);

  dbms_scheduler.set_job_argument_value(
    job_name => c_job_name,
    argument_name => '"DECRYPT_FILE"."P_INPUT_FILE"',
    argument_value => '/some/filesystem/location/file12345.pgp');
END;
/

I've seen references to the idea of a driver table where the key is the job name (and sub name?) but I cannot seem to find an example of this. MOS article 1272728.1 talks about using DEFINE_METADATA_ARGUMENT which I think I have just about got my head around. I haven't tried this yet as it refers to Oracle 10.2 - 11.1 so does this still apply for 19c?
Thanks in advance
Gareth

This post has been answered by Gareth S on Feb 14 2024
Jump to Answer
Comments
Post Details
Added on Sep 8 2022
1 comment
229 views