1 Reply Latest reply: Aug 6, 2014 6:34 AM by GregV RSS

    Problem with interdepentdent scheduler jobs in different schema

    vijay29

      I have 2 normal db user stg and user  (they dont have dba privileges), Both of them can create dbms_scheduler jobs

       

      I want to execute a package from stg schema by dbms_scheduler job.

      After successful completion of that job in stg, scheduler jobs in

      user schema should execute.

       

      I cannot give grant of user package to stg because it have authid current_user

      where we have truncated drop statements.

       

      For testing purpose I just inserting into tables.After that I will replace that with package call.

       

      How to acheive this?

       

       

      Till now I have acheive this, But i have problem with jobs in user schema.

      They gives error(mentioned inline)

       

       

      in STG schema:-

      create table vijay_job_output (a timestamp with time zone, b varchar2(30));

       

       

      Exec Dbms_Scheduler.Add_Event_Queue_Subscriber('Datamart');

       

       

      Begin

      --Dbms_Scheduler.drop_Job('FIRST_JOB');

      Dbms_Scheduler.Create_Job

          ( 'first_job',

            Job_Action => 'insert into stg.vijay_job_output values(systimestamp, ''first job runs'');',

            Job_Type => 'plsql_block',

            Enabled => True,

            Start_Date=> Trunc(Sysdate)+14.3/24, -- start today 3:30 (3:30 a.m.)

            repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=14; BYMINUTE=18;'

          ) ;

      dbms_scheduler.set_attribute( 'first_job' , 'raise_events' , dbms_scheduler.job_run_completed);

      End;

      /

       

       

      In USER schema

      create table vijay_job_output (a timestamp with time zone, b varchar2(30));

       

       

      -- create a simple second job that runs after the first has completed

      begin

      dbms_scheduler.create_job('second_job',

      job_type=>'plsql_block',

      job_action=>'insert into user.vijay_job_output values(systimestamp, ''second job runs'');',

      Event_Condition =>'tab.user_data.object_name = ''FIRST_JOB''',

      queue_spec =>'sys.scheduler$_event_queue,Datamart',

      Enabled=>True);

      dbms_scheduler.set_attribute( 'second_job' , 'raise_events' , dbms_scheduler.job_run_completed);

      End;

      /

      error:-

      ORA-27375: valid agent name must be specified for secure queues

      ORA-06512: at "SYS.DBMS_ISCHED", line 124

      ORA-06512: at "SYS.DBMS_SCHEDULER", line 314

      ORA-06512: at line 2

       

       

      Begin

      dbms_scheduler.create_job('third_job',

      job_type=>'plsql_block',

      job_action=>'insert into user.vijay_job_output values(systimestamp, ''third job runs'');',

      Event_Condition =>'tab.user_data.object_name = ''SECOND_JOB''',

      queue_spec =>'sys.scheduler$_event_queue,Datamart',

      enabled=>true);

      End;

      /

       

       

      error:-

      ORA-27375: valid agent name must be specified for secure queues

      ORA-06512: at "SYS.DBMS_ISCHED", line 124

      ORA-06512: at "SYS.DBMS_SCHEDULER", line 314

      ORA-06512: at line 2