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:
The "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