Oracle PL/SQL for DBAs

Managing Attributes

Throughout this chapter I have illustrated how you can assign properties for various scheduling components at the time you create them—for example, with the CREATE_JOB or CREATE_WINDOW commands. Sometimes, however, you may have to change the properties of a scheduling component after it has been created. For example, you may want to change the repeat interval of a job that has already been created in the database. One approach to changing properties is to drop and then re-create the object, but this is not always an option.

Properties such as repeat intervals, schedule names, logging level, and so on are known as the attributes of a job, program, or schedule object. The DBMS_SCHEDULER package provides the SET_ATTRIBUTE procedure , which allows you to change the attributes of an existing job, job class, program, schedule, window, or window group.

For example, suppose that I want to change the comments for the job PURGE_LOG to “This job purges the log entries of jobs and windows”. I could issue the following:

    BEGIN
       DBMS_SCHEDULER.set_attribute
                           ('PURGE_LOG',
                            'comments',
                            'This job purges the log entries of jobs and windows'
                           );
    END;

This procedure accepts three parameters:

component_name

Name of the component whose property is to be changed. Because jobs, windows, programs, job classes, window groups, and schedules all have unique names, there is no need to specify the type of component here.

attribute

Property to be changed as a VARCHAR2 datatype. ...