A metadata argument is an argument that tells something about the currently running job.

There are several reasons for using metadata arguments in a program. One is that a program is executed by several different jobs and you want to know which job called the program. The job_name is one of the important metadata attributes for a metadata argument. The complete list of useable attributes is as shown in the following table:

This does look a bit mysterious, but a simple example will make things clearer. In this example, we make a small program that uses the job_name attribute to find out which job called the program.

First, give marvin the required privileges and quota to be able to create a log table:

Grant create table to marvin;

Alter user marvin quota unlimited on users;

Next, let marvin create the log table:

Create table log (job_name varchar2(30), dat_sys date);

Define a stored procedure that accepts one argument, the job_name:

--/
CREATE OR REPLACE PROCEDURE WHOCALLEDME (v_job varchar2) as
begin
insert into log (job_name, dat_sys) values (v_job, sysdate);
end WHOCALLEDME;
/

Now, create a program that uses this stored procedure:

--/
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => '"MARVIN"."P_CALLER"',
program_action => '"MARVIN"."WHOCALLEDME"',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => 'show which job called me',
enabled => FALSE
);
end;
/

This program is created with enabled as FALSE. As not all of the arguments are defined, it cannot be enabled. Trying to run this code with enabled=> TRUE will result in an error message that will try to explain to us that not all arguments are defined. Now it is time to define the metadata argument to complete the job.

--/
begin
DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT
(
program_name => 'P_CALLER',
metadata_attribute => 'job_name',
argument_position => 1,
argument_name => 'v_job'
);
end;
/

The program is now completely defined with all of the arguments, so we can enable the program. If this program is called by some job, the Scheduler automatically inserts the correct values in the first argument of this program.

--/
BEGIN
dbms_scheduler.enable('P_CALLER');
END;
/

Now create the job that uses the enabled program, P_CALLER. The job name is test_m_arg and is defined as follows:

--/
BEGIN
dbms_scheduler.create_job
(
job_name => 'TEST_M_ARG',
program_name => 'P_CALLER',
comments => 'test using metadata argument',
enabled => TRUE
);
END;
/

Because the job is enabled at the creation time and we have not tied the job to a schedule, an event, or a window, the job immediately starts running. So it makes sense to check the contents of the log table. Despite the fact that the job itself has no arguments, the job name is passed to the program that inserted it into the log table.

Select job_name from log;

This clearly reveals the name of the job that caused the program to be executed: TEST_M_ARG.

This example might look a bit silly, but this mechanism gives us countless possibilities. The other attributes have their own different uses. For example, the window_end attribute enables a job to find out how much time it has to complete its task before the window that started the job will close. This can help the code to decide whether or not to make an extra iteration to complete another batch of transactions.

Now that we have seen the mysterious metadata arguments, the normal application arguments, also known as regular arguments, are just a piece of cake. For this example, we use the same log table as for the metadata example. We create a separate procedure that also has one argument as follows:

--/
CREATE OR REPLACE PROCEDURE justaproc (v_arg varchar2) as
begin
insert into log (job_name, dat_sys) values (v_arg, sysdate);
end justaproc;
/

The procedure used by a program is defined as follows:

--/
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'P_ARG01',
program_action => 'JUSTAPROC',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => 'pass an argument',
enabled => FALSE
);
END;
/

Now let's define the argument to complete the program description:

--/
BEGIN
DBMS_SCHEDULER.DEFINE_program_ARGUMENT
(
program_name => 'P_ARG01',
argument_position => 1,
argument_name => 'v_arg',
argument_type => 'varchar2'
);
END;
/

The argument type is mandatory, but it is not the type checked by the Scheduler. So, if you are going to use it, you need to test the arguments definition before using it. Now that the program is complete, we can enable it.

--/
BEGIN
dbms_scheduler.enable('P_ARG01');
END;
/

We are reaching the goal of creating a job that passes an argument to the code. Everything is in place, so create the job now:

--/
BEGIN
sys.dbms_scheduler.create_job
(
job_name => 'TEST_ARG',
program_name => 'P_ARG01',
comments => 'test using a regular argument',
enabled => FALSE
);
END;
/

You might have noticed that the job is created in the disabled state. This is the same as for the program arguments—all of the job arguments have to be defined and given a value before the job can be enabled. Failing to do so will result in ORA-27457: argument 1 of job "MARVIN.TEST_ARG" has no value. We don't want such errors, so we define the arguments before trying to enable—and run—the job:

--/
BEGIN
dbms_scheduler.set_job_argument_value
(
job_name => 'TEST_ARG',
argument_name => 'V_ARG',
argument_value => 'manual'
);
END;
/

And finally we are ready to enable the job. The job will start immediately, so we can check the log table right away. First enable the job like this:

--/
BEGIN
dbms_scheduler.enable('TEST_ARG');
END;
/

If everything goes as expected, you will find another entry in the log table with "manual" in the job_name column. If you are experimenting with job arguments, you might notice that you don't need to disable a job to give its arguments a new value. As soon as you do, the job automatically becomes invalid. When you are ready, the job will not automatically get enabled again and you need to do so manually.