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 ...