The CREATE_PROGRAM procedure creates a program.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name IN VARCHAR2,
program_type IN VARCHAR2,
program_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,
enabled IN BOOLEAN DEFAULT FALSE,
comments IN VARCHAR2 DEFAULT NULL);
Parameters
| Parameter | Description |
|---|---|
| program_name | The name of the program to be created. The name must be unique in the SQL namespace. For example, the program cannot have the same name as a table in the schema. If you do not specify the name, an error is returned. |
| program_type | The type of the program to be created. If you do not specify the type, an error is returned. You can specify only 'STORED_PROCEDURE', which indicates that the program is a PL or Java stored procedure or an external C subprogram. This parameter does not support functions that return values or PL procedures that contain INOUT or OUT parameters. |
| program_action | The action of the program. If you do not set the program_action parameter, an error is returned. For a stored procedure, the program_action parameter indicates the name of the stored procedure. If the procedure is located in a schema that does not belong to the job, you must specify the schema name. If case sensitivity is required, enclose the schema name and the stored procedure name in double quotation marks (" "), for example, job_action_proc=>'"Schema"."myProc"'. |
| number_of_arguments | The number of arguments the program requires. If you do not set this parameter, the default value 0 is used. Value range: [0,255]. |
| enabled | Specifies whether the created program is enabled. You can set this parameter to TRUE or FALSE. The default value is FALSE, which indicates that the created program is disabled. If you set this parameter to TRUE, the program is created and enabled after the validity check is passed. By default, this parameter is set to FALSE, which indicates that the created program is disabled. You can also call the ENABLE procedure to enable the program. |
| comments | The comments on the program. The default value is NULL. |
Considerations
By default, a program is created in the disabled state and cannot be executed by a job. To create a program as enabled, set the enabled parameter to TRUE. To use your program, other users must have the EXECUTE privilege.
Examples
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
PROGRAM_NAME => 'empprogram',
PROGRAM_TYPE => 'STORED_PROCEDURE',
PROGRAM_ACTION => 'hr.empprogram',
COMMENTS => 'Employee data management');
END;