9.4 Programs

The Programs page displays the list of created programs.

The Actions menu for a program consists of the following options: Edit, Drop, Enable or Disable the program and View Program Details, including used procedure and procedure dependencies.

Description of the illustration program_procedure_dependencies.png

9.4.1 Create or Edit Program

This section describes how to create or edit an Oracle Scheduler program.

To create a program, Database Actions internally uses the DBMS_SCHEDULER.CREATE_PROGRAM procedure.

1. In the Programs page, at the top right, click Create Program.
2. In Program Properties, enter the following fields:

   Details tab

   • Name: Name of the program. The name has to be unique in the SQL namespace. For example, a program cannot have the same name as a table in a schema.

   • Enabled: If this option is specified, validity checks will be made and the program will be created enabled if all the checks are successful. If this option is not specified, the program is not created enabled.

   • Description: Optional text string that can be used to describe the program.

   • Type

     • PL/SQL Block: The program is a PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0. Enter or paste in the complete PL/SQL code, or edit the existing code.

     • Stored Procedure: The program is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported. PL/SQL procedures with IN OUT or OUT arguments are not supported.

       Schema: Schema of the stored procedure. If not specified, the schema of the job is assumed.

       Procedure: Name of the stored procedure.

       Arguments: For each argument, name, data type, default value, and whether it is an input, output, or input/output argument.

     • Script: The program is a SQL Script (SQL*Plus statements), Backup Script (RMAN commands), or External Script (operating system commands). Enter or paste the script text in the box.

   Properties tab

   Enables you to set program properties. For most properties the default is null, but you can check the box to specify a value.

   • Detached: Enabled if the program is a detached job. Use a detached job to start a script or application that runs in a separate process, independently and asynchronously to the Scheduler. A detached job typically starts another process and then exits. Upon exit (when the job action is completed) a detached job remains in the running state. The running state indicates that the asynchronous process that the job started is still active. When the asynchronous process finishes its work, it must connect to the database and call DBMS_SCHEDULER.END_DETACHED_JOB_RUN, which ends the job.

   • Max Runs: Maximum number of runs before the program is marked as completed.

   • Max Failures: Maximum number of failures tolerated before the program is marked as broken.

   • Max Run Duration: Maximum run duration of the program.

   • Schedule Limit: Maximum delay time between scheduled and actual job start before a program run is canceled.

3. In the DDL pane, you can review and save the SQL statements that are generated.

   • For a new program, click CREATE to view the generated DDL statements.

   • When you edit a program, click UPDATE to view the generated ALTER statements.

   When you are finished, click Apply.

4. The Output pane displays the results of the DDL commands. If there are any errors, go to the corresponding pane, fix the errors, and run the commands again. You can save to a text file or clear the output.