The SET_ATTRIBUTE procedure is used to modify the attributes of a Scheduler object.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only 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 | See Attribute values below. |
| value | The new value for the attribute, which cannot be NULL. |
Property value
Job attribute values
The current version of OceanBase Database supports the following job-related attribute values.
| Property name | Description |
|---|---|
| auto_drop | This property can be set to TRUE or FALSE. If it is set to TRUE, the job will be automatically dropped after it completes or is disabled. A job is considered completed if any of the following conditions are met:
max_failures times, it will be disabled. max_failures is also set using SET_ATTRIBUTE. If auto_drop is set to FALSE, the job will not be dropped and its metadata will be retained until it is explicitly dropped using the DROP_JOB procedure. By default, auto_drop is set to TRUE when a job is created.
NoteThis property is not supported in the current version. |
| comments | Optional comments.
NoteThis property is not supported in the current version. |
| credential_name | The name of the credential object used for the job. For local database jobs, this value must be NULL. For local external jobs, if this property is NULL (default), the preferred (default) credential will be selected.
NoteThis property is not supported in the current version. |
| destination_name | The database or external destination for the job. This is applicable only for remote database and remote external jobs. For jobs running on a local database or local external job (executable), this property must be NULL.
NoteThis property is not supported in the current version. |
| end_date | The date and time when the job will expire and no longer run. After end_date, if auto_drop is TRUE, the job will be dropped. If auto_drop is FALSE, the job will be disabled, and its STATE will be set to COMPLETED. If end_date is not specified, the job will continue to run unless max_runs or max_failures is set. In that case, the job will stop when either of these values is reached. The value of end_date must be after start_date, or an error will occur. If end_date is the same as start_date, the job will not execute, but no error will be returned.
NoteThis property is not supported in the current version. |
| job_action | The action performed by the job, which depends on the job_type property. For example, if the job type is STORED_PROCEDURE, job_action contains the name of the stored procedure.
NoteThis property is not supported in the current version. |
| job_class | The class associated with the job. |
| job_name | The name of the job. This name must be unique within the SQL namespace. If the job belongs to another schema, the schema name must be specified. If a job name is not provided, an error will occur. You can also use the GENERATE_JOB_NAME procedure to generate a name, which is a sequence-generated number that can be prefixed with a string. The generated job name, which includes the sequence number, is then used in the CREATE_JOB procedure.
NoteThis property is not supported in the current version. |
| job_priority | The priority of the job relative to other jobs in the same class. If multiple jobs in a class are scheduled to run simultaneously, the job priority determines the execution order. It can be a value between 1 and 5, with 1 being the highest priority. If the job priority is not specified when creating the job, the default priority is 3.
NoteThis property is not supported in the current version. |
| job_type | The type of the job to be created. If not specified, an error will occur. Currently, only STORED_PROCEDURE is supported. If this option is set, program_name must be NULL.
NoteThis property is not supported in the current version. |
| max_failures | The maximum number of consecutive failures before the job is automatically disabled. Once disabled, the job will no longer execute. max_failures can be an integer between 1 and 1,000,000. The default is NULL, which means a new instance of the job will start regardless of how many times previous instances failed. |
| max_runs | The maximum number of consecutive runs for the job. Once this limit is reached, the job will be disabled, and its status will change to COMPLETED. The maximum number of runs can be an integer between 1 and 1,000,000. The default is NULL, which means the job will run indefinitely until it reaches end_date or max_failures.
NoteThis property is not supported in the current version. |
| max_run_duration | The maximum duration for which the job can run. |
| number_of_argument | The number of parameters for the inline program job. If this option is set, program_name must be NULL.
NoteThis property is not supported in the current version. |
| program_name | The name of the program associated with the job. If the program type is EXECUTABLE, then job_action, job_type, and number_of_arguments must be NULL.
NoteThis property is not supported in the current version. |
| repeat_interval | A PL function that returns the next run date and time, or a calendar syntax expression. If this option is set, schedule_name must be NULL.
NoteThis property is not supported in the current version. |
| start_date | The initial date and time when the job starts or is scheduled to start. If this option is set, schedule_name must be NULL.
NoteThis property is not supported in the current version. |
| instance_id | By default, jobs are executed in the Primary Zone. The instance_id parameter specifies the zone in which the job is executed. If instance_id is set to RANDOM (i.e., the value parameter in the procedure is set to RANDOM), the job is randomly distributed across all zones. |
Procedure attributes
| Parameter | Description |
|---|---|
| comments | Optional. Describes the function or usage details of the procedure. |
| number_of_arguments | The number of parameters required by the procedure. |
| program_type | The type of the procedure. Currently, only STORED_PROCEDURE is supported. |
| program_action | The operation performed by the procedure, specified by the program_type attribute. For example, if the procedure type is STORED_PROCEDURE, then program_action contains the name of the stored procedure. |
Considerations
If you want to change an enabled object, the Scheduler disables it, makes the change, and then reenables it. If an error occurs during the reenable process, the object remains disabled and an error message is generated. If you change a disabled object, the object remains disabled after the change.
For jobs:
If a job has a running instance when you call
SET_ATTRIBUTE, the instance is not affected by the call. The changes only affect the job's future executions.If you change any schedule-related attributes of a running job, the changes will be used to schedule the next execution of the job. Schedule-related attributes include
schedule_name,start_date,end_date, andrepeat_interval.If you change any program-related attributes of a running job, the changes will take effect during the next execution of the job. Program-related attributes include
program_name,job_action,job_type, andnumber_of_arguments.If you change any job parameters during the execution of a job, the changes will take effect during the next execution of the job.
If you change the schedule, the changes will not affect the currently running job. The changes will take effect only during the next execution of the job.
For programs, if you modify the program that a job is currently running, the job will continue to use the previous attribute values. During the next execution of the job, the job will use the new attribute values.