Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

A Simple Example

Suppose that I am working with a banking application and want to schedule the execution of a job that applies interest daily to the account balances. A stored procedure called apply_interest is used to apply the interest. I can use DBMS_SCHEDULER’s CREATE_JOB program to create a scheduler job named “apply_daily_interest” as follows:

      1  BEGIN
      2     DBMS_SCHEDULER.create_job (job_name => 'apply_daily_interest',
      3                   job_type              => 'STORED_PROCEDURE',
      4                   job_action            => 'apply_interest',
      5                   repeat_interval       => 'FREQ=DAILY; INTERVAL=1',
      6                   enabled               => TRUE,
      7                   comments              => 'Apply Daily Interest'
      8                  );
      9* END;

Let’s examine the lines in detail in the following table.

Line

Description

2

I define the name of the job. The name must be a valid Oracle identifier, which means that it must start with a letter; contain some combination of letters, numbers, #, _, or $; and cannot be longer than 30 characters. The name also must be unique in the Oracle namespace; it cannot, for example, have the same name as a table.

3

Here I specify what type of program is to be run. This is a wonderful enhancement of DBMS_SCHEDULER over the functionality available in DBMS_JOB. In this case, the program to be executed (apply_interest) is a stored procedure, indicated by the value (“STORED_PROCEDURE”) of the job_type parameter. Other valid values for this argument are “PLSQL_BLOCK” and “EXECUTABLE”.

4

The job_action parameter indicates what this job does—in this case, it executes the stored procedure apply_interest. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page