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

Running Other Users’ Programs

As I described when covering schedules, programs can be owned by users other than their creators. By default, a program defined in the CREATE_JOB procedure is assumed to be owned by the user creating the job. However, if you want, you can use a program owned by another user. For example, suppose that a user known as INTEREST_ADMIN owns the program to apply interest as follows:

    SQL> CREATE USER interest_admin IDENTIFIED BY interest_admin;
    User created.

    SQL> GRANT CREATE SESSION, CREATE JOB, CREATE PROCEDURE to interest_admin;
    Grant succeeded.

    SQL> CONN interest_admin/interest_admin
    Connected.

    SQL> CREATE PROCEDURE calc_int
      2  AS
      3  BEGIN
      4     NULL;
      5  END;
      6  /

    Procedure created.

I can now use this procedure in a named program called CAL_INTEREST as the user INTEREST_ADMIN:

    BEGIN
       DBMS_SCHEDULER.create_program (program_name        => 'CALC_INTEREST',
                                      program_type        => 'STORED_PROCEDURE',
                                      program_action      => 'calc_int',
                                      enabled             => TRUE,
                                      comments            => 'Calculate Interest'
                                     );
    END;

Once the program has been created, I can create a schedule owned by the user SCHED_MANAGER. The schedule is named EVERY_DAY.

    SQL> CONN sched_manager/sched_manager
    Connected.
    SQL> BEGIN
      2     DBMS_SCHEDULER.create_schedule (schedule_name    => 'every_day',
      3                                     start_date       => SYSTIMESTAMP,
      4                                     repeat_interval  => 'FREQ=DAILY; BYHOUR=3',
      5                                     comments       => 'Schedule Run for Int Calc'
      6                                    );
      7  END;
      8  /

    PL/SQL procedure successfully completed.

Before user ACC_MANAGER can use this program, she must be granted the EXECUTE privilege ...

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