Let's assume we want to create a set of jobs, all of which execute a stored procedure stored in the database at different times.

In such a scenario, you can create all these jobs using the CREATE_JOB procedure, where the JOB_TYPE is set to STORED_PROCEDURE and the JOB_ACTION takes the name of the procedure we want to execute. This process will be repeated for all the jobs we want to create, all of which will be linked to the same stored procedure.

This approach can be cumbersome and involves a certain degree of repetition. So, what is the solution?

The solution is to create a PROGRAM, which contains the information of the procedure we want to execute, and then we simply call it from the job.

Here, we will use a procedure called CREATE_PROGRAM belonging to the DBMS_SCHEDULER package. Note that the variables required are not many; we only need to specify the PROGRAM_NAME, which must be unique in this schema. We also need to determine the PROGRAM_TYPE, which corresponds to the JOB_TYPE, and the PROGRAM_ACTION, which corresponds to the JOB_ACTION. The remaining variables (NUMBER_OF_ARGUMENTS, ENABLED, and COMMENTS) have already been explained previously.

begin

dbms_scheduler.create_program(

program_name=>'prog_account',

program_type=>'stored_procedure',

program_action=>'account',

enabled=>true);

end;

We created a program named prog_program. This program specifies the type of task we want to execute later when creating the job. This task is a stored procedure stored at the database level, and this procedure is named account.

We can monitor all programs in the database using the view DBA_SCHEDULER_PROGRAMS.

If the variable Enabled is not set to True, it defaults to False, meaning it will be disabled.

You can activate it later using the ENABLE procedure available in the DBMS_SCHEDULER package. This procedure can be used not only for programs but also to enable other scheduling entities.

We can also delete the program from the database using the DROP_PROGRAM procedure. 

BEGIN

DBMS_SCHEDULER.DROP_PROGRAM(

PROGRAM_NAME=>'PROG_ACCOUNT',FORCE=>TRUE);

END;

/ 

Thus, we deleted the program that we had previously created.