The SET_ATTRIBUTE procedure modifies the attributes of a Scheduler object.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
DBMS_SCHEDULER.SET_ATTRIBUTE (
job_name IN VARCHAR2,
name IN VARCHAR2,
value IN VARCHAR2);
Parameters
| Parameter | Description |
|---|---|
| name | The name of the object. |
| attribute | For more information, see the following Attribute values section. |
| value | The new value that is set for the attribute. The value of this parameter cannot be NULL. |
Attribute values
Values of job attributes
OceanBase Database supports the following job attributes:
| Attribute | Description |
|---|---|
| auto_drop | Valid values: TRUE and FALSE. If you set it to TRUE, the job is automatically dropped or disabled after it is completed. A job is considered completed if one of the following conditions is met:
max_failures times. You must call the SET_ATTRIBUTE procedure to set the max_failures attribute. If you set the auto_drop attribute to FALSE, the job is not dropped, and its metadata is retained. You can call the DROP_JOB procedure to explicitly drop the job. By default, the auto_drop attribute is set to TRUE when you create a job. |
| comments | The comments about the job. This attribute is optional. |
| credential_name | The name of the credential object that is used for the job. For a local database job, the value must be NULL. For a local external job, if the default value NULL is used, the default credential is selected. |
| destination_name | The destination database or external destination of the job. This attribute is available only for remote database jobs and remote external jobs. For a job running on a local database or a local external job (an executable file), the value must be NULL. |
| end_date | The timestamp that consists of the date and time after which the job expires and no longer runs. If the auto_drop attribute is set to TRUE, the job is dropped after the time specified in end_date. If the auto_drop attribute is set to FALSE, the job is disabled, and the status of the job is set to COMPLETED. If you do not set the end_date attribute, the job is repeatedly executed. In this case, if you set the max_runs or max_failures attribute, the job stops running when either of the values is reached. The time specified in the end_date attribute must be later than that in the start_date attribute. Otherwise, an error is returned. If the value of the end_date and start_date attributes are the same, the job is not executed, and no error is returned. |
| job_action | The operation that the job performs. The value depends on the job_type attribute. For example, if the job type is STORED_PROCEDURE, the job_action attribute contains the name of the stored procedure. |
| job_class | The class to which the job belongs. |
| job_name | The name of the job. The name must be unique in the SQL namespace. If you are creating a job that belongs to another schema, you must qualify it with the schema name. If you do not specify the job name, an error is returned. You can also call the GENERATE_JOB_NAME procedure to use the Scheduler to generate a name, which is a serial number generated in order. You can prefix a string to the number. As a result, the job name is a string with a serial number and can be used in the CREATE_JOB procedure. |
| job_priority | The priority of the job in its class. If multiple jobs in a class are scheduled to execute at the same time, the execution order is determined by the job priority. The attribute value can be an integer from 1 to 5. The value 1 indicates that the job is the first to be executed. If you do not specify the job priority during job creation, the default job priority is 3. |
| job_type | The type of the job. If you do not specify the job type, an error is returned. You can specify only STORED_PROCEDURE. If you set this parameter to STORED_PROCEDURE, the value of the program_name attribute must be NULL. |
| max_failures | The number of times the job can fail on consecutive runs before it is automatically disabled. Once a job is disabled, it is no longer executed. The value of the max_failures attribute can be an integer from 1 to 1,000,000. The default value is NULL, which indicates that a new instance is started for the job regardless of the number of previously failed instances. |
| max_runs | The maximum number of times the job can consecutively run. Once the specified maximum number of times is reached, the job is disabled, and its status changes to COMPLETED. The attribute value can be an integer from 1 to 1,000,000. The default value is NULL, which indicates that the execution is repeated forever or until the value of the end_date or max_failures attribute is reached. |
| number_of_argument | The number of job arguments of the associated program. If you set this attribute, the value of the program_name attribute must be NULL. |
| program_name | The name of the program that is associated with the job. If the program type is EXECUTABLE, the value of the job_action, job_type, and number_of_arguments attributes must be NULL. |
| repeat_interval | The procedural language (PL) function or calendar syntax expression that returns the next runtime of the job (including the date and time). If you set this attribute, the value of the schedule_name attribute must be NULL. |
| start_date | The initial date and time when the job started or is scheduled to start. If you set this attribute, the value of the schedule_name attribute must be NULL. |
Values of program attributes
| Attribute | Description |
|---|---|
| comments | The comments about the job. This attribute is optional. You can add comments to describe the program features or the details about the program usage. |
| number_of_arguments | The number of arguments the program requires. |
| program_type | The type of the program. Only the STORED_PROCEDURE type is supported. |
| program_action | The operation performed by the program. The operation is indicated by the program_type attribute. For example, if the program type is STORED_PROCEDURE, the program_action attribute contains the name of the stored procedure. |
Considerations
If you change an enabled object, the Scheduler disables it, changes it, and then re-enables it. If an error occurs during enabling, the object is not re-enabled, and an error message is returned. If you change a disabled object, the object remains disabled after the change.
For jobs:
If a job has a running instance while calling the
SET_ATTRIBUTEprocedure, the instance is not affected by the call, and the changes affect only the operations of the job after the call.If a schedule attribute of the job is changed during the running of the job, the next runtime is scheduled based on the new attribute values. The schedule attributes of a job include
schedule_name,start_date,end_date, andrepeat_interval.If a program attribute of the job is changed during job running, the new attribute value takes effect for the next running of the job. The program attributes of a job include
program_name,job_action,job_type, andnumber_of_arguments.If an attribute of the job is changed during job running, the new attribute value takes effect for the next running of the job.
If the schedule of a job is changed during job running, the change does not affect the current running and takes effect for the next running.
If a running program of a job is changed, the program runs based on the attribute values before the change. The new attribute values take effect for the next running of the job.
