Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

   
 

dbms_scheduler.create_program tips

Oracle Job Scheduling

Chapter 2 - Configuring Oracle Job Scheduling

Programs

  • program_type - The type of action associated with this program (PLSQL_BLOCK, STORED_PROCEDURE or EXECUTABLE).
     

  • program_action - The actual work that is done by the program.
     

  • number_of_arguments - The number of arguments required by this program.  Programs using arguments must have their arguments defined before they can be enabled.
     

  • enabled - A flag which indicates if the program is enabled or not.  If the program accepts arguments, it cannot be enabled until the arguments are defined.
     

  • comments - Free text, allowing the user to record additional information.

The action of a program may be a PL/SQL block, a stored procedure or an OS executable file.  The following examples show how each type of program is defined.

BEGIN
  -- PL/SQL Block.
  DBMS_SCHEDULER.create_program (
    program_name   => 'test_plsql_block_prog',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)'');
END;',
    enabled        => TRUE,
    comments       => 'CREATE_PROGRAM test using a PL/SQL block.');
END;
/


Programs can be created using the Create Program screen of the OEM DB Control as shown in Figure 11.14.

 

Figure 11.14 - OEM DB Control: Create Program

 

Information about programs can be displayed using the dba_scheduler_programs view. The following script uses this view to display basic information about the currently defined programs.

 

            programs.sql

 

set verify off

 

select

   owner,

   program_name,

   enabled

from

   dba_scheduler_programs

where

   owner = decode(upper('&1'), 'ALL', owner, upper('&1'));

 

The programs.sql script can display all programs or only those programs of a specified user.

 

SQL> @programs all

 

OWNER                          PROGRAM_NAME                   ENABL

------------------------------ ------------------------------ -----

SYS                            PURGE_LOG_PROG                 TRUE

SYS                            GATHER_STATS_PROG              TRUE

JOB_USER                       TEST_PLSQL_BLOCK_PROG          TRUE

JOB_USER                       TEST_STORED_PROCEDURE_PROG     TRUE

JOB_USER                       TEST_EXECUTABLE_PROG           TRUE

 

SQL> @programs job_user

 

OWNER                          PROGRAM_NAME                   ENABL

------------------------------ ------------------------------ -----

JOB_USER                       TEST_PLSQL_BLOCK_PROG          TRUE

JOB_USER                       TEST_STORED_PROCEDURE_PROG     TRUE

JOB_USER                       TEST_EXECUTABLE_PROG           TRUE

 

Information about program arguments can be displayed using the dba_scheduler_program_args view.  The following script uses this view to display information about the arguments of currently defined programs.

 

program_args.sql

 

-- *************************************************

-- Parameters:

--    1) Specific USERNAME or ALL which doesn't limit output.

--    2) Program name.

-- *****************************************************************

 

set verify off

column argument_name format a20

column default_value format a30

 

select

   argument_position,

   argument_name,

   default_value

from

   dba_scheduler_program_args

where

   owner = decode(upper('&1'), 'ALL', owner, upper('&1'))

and

   program_name = upper('&2');

 

The output from the program_args.sql script is displayed below.

 

SQL> @program_args job_user test_stored_procedure_prog

 

ARGUMENT_POSITION ARGUMENT_NAME        DEFAULT_VALUE

----------------- -------------------- -----------------------------

                1 P_TEXT               This is a default value.

 

Programs that are no longer used can be removed using the drop_program procedure whose call specification is listed below.

 

PROCEDURE drop_program (

  program_name            IN VARCHAR2,

  force                   IN BOOLEAN DEFAULT FALSE)

 

The parameters associated with this procedure and their usage are as follows:

  • program_name - A name that uniquely identifies the program

  • force - When set to TRUE, all jobs which reference the program are disabled prior to the program being dropped.  If set to FALSE and jobs reference the program, an error is produced.  In addition, all program arguments information is dropped.

The following examples show how the drop_program  procedure is used.

 

BEGIN

  DBMS_SCHEDULER.drop_program (program_name => 'test_plsql_block_prog');

  DBMS_SCHEDULER.drop_program (program_name => 'test_stored_procedure_prog');

  DBMS_SCHEDULER.drop_program (program_name => 'test_executable_prog');

END;

/

 

One can determine that the programs have been removed by checking the output of the programs.sql script.

 

SQL> @programs all

 

OWNER                          PROGRAM_NAME                   ENABL

------------------------------ ------------------------------ -----

SYS                            PURGE_LOG_PROG                 TRUE

SYS                            GATHER_STATS_PROG              TRUE

 

Program information is also available from the OEM DB Control via the Scheduler Programs screen shown in Figure 11.15.

 

Figure 11.15 - OEM DB Control: Sheduler Programs

 

Now that defining reusable programs has been explained, the next section will explain the defining of reusable schedules.


��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.