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 (
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN VARCHAR2);
Parameters
| Parameter | Description |
|---|---|
| name | The name of the object. |
| attribute | For more information, see the following Attributes section. |
| value | The new value that is set for the attribute. The value of this parameter cannot be NULL. |
Attributes
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 after it is completed or automatically disabled. A job is considered completed if one of the following conditions is met:
max_failures. 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 by end_date elapses. 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 have 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 values of the end_date and start_date attributes are the same, the job will not be 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 values 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 starts or is scheduled to start. If you set this attribute, the value of the schedule_name attribute must be NULL. |
| instance_id | The zone where the job is executed. By default, the job is executed in the primary zone. If you set the value to RANDOM, namely, set the value parameter of the subprogram to RANDOM, the job is randomly sent to all zones. |
Program attributes
| Attribute | Description |
|---|---|
| comments | The comments about the program. 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 specified 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 modify an enabled object, the Scheduler disables it, modifies 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 modify a disabled object, the object remains disabled after the modification.
For jobs:
If a job has a running instance while calling the
SET_ATTRIBUTEprocedure, the instance is not affected by the call, and the modifications affect only the operations of the job after the call.If a schedule attribute of a job is modified 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 a job is modified 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 a job is modified during job running, the new attribute value takes effect for the next running of the job.
If the schedule of a job is modified during job running, the modification does not affect the current running and takes effect for the next running.
If a running program of a job is modified, the program runs based on the attribute values before the modification . The new attribute values take effect for the next running of the job.