Defining arguments for your jobs

When using building blocks like stored procedures and programs, we might want to control what the code is going to do for us by giving it arguments. Arguments can also be given to external jobs such as command-line arguments. There are two types of arguments:

• Metadata arguments

• Normal application arguments

If you are using arguments for a job, you will start by specifying the number of arguments that the procedure, program, or external job is going to have. Next, you need to define the arguments and their values. Until all of the arguments are defined, it is not possible to enable the item for which you are defining the arguments.

Metadata arguments

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:

Attribute name	Description
job_name	Name of the currently running job.
job_subname	Subname of the currently running job, if running in a chain. The combination of job_name and job_subname define the step that the chain is running.
job_owner	The owner of the currently running job.
job_scheduled_start	This tells when the job was scheduled to start.
job_start	This tells when the job really started.
window_start	If the job is connected to a window, this specifies the window open time.
window_end	If the job is started by a window, this specifies the time at which the window is scheduled to close.
event_message	The contents of the event message that triggered an event-driven job.

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.

Normal application arguments

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.