Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle CREATE_PROGRAM Procedure
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.