hashir hashir -4 years ago 91
SQL Question

Execute an Oracle Schedule-Program through an Oracle Schedule-Job

I know that in order to schedule any action on a database table i must form a schedule chain, that is, a job (DBMS_SCHEDULER.CREATE_JOB) that links a schedule(DBMS_SCHEDULER.CREATE_SCHEDULE) and a program(DBMS_SCHEDULER.CREATE_PROGRAM). But what if i want to accomplish the same task only through a PROGRAM and a linked JOB ? Is it possible ? I have tried and when i check the PROGRAM log it shows me :

RUNS FAILS
======================
10 10


Which means that the JOB has been executed for 10 times (runs at an interval of 5 secs.) and it has failed for the same number of times.

Following are the codes for concerned JOB and PROGRAM :

PROGRAM-CODE :
=================

BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'HR.INSERT_PROG'
,program_type => 'PLSQL_BLOCK'
,program_action => 'insert into sch_test values (s1.nextval,s2.nextval)'
,number_of_arguments => 0
,enabled => TRUE
,comments => NULL
);
END;


JOB-CODE :
==========

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'HR.JOB1'
,schedule_name => 'HR.INSERT_SCH'
,program_name => 'HR.INSERT_PROG'
,comments => NULL
);
END;


The concerned table never gets populated. If anyone can tell me, what is it i am doing wrong here? Can this not be achieved without a SCHEDULE ?

Answer Source

You don't have to create separate PROGRAM and SCHEDULE, you can put all in one JOB like this:

DBMS_SCHEDULER.CREATE_JOB
(
   job_name        => 'HR.JOB1'
  ,start_date      => LOCALTIMETAMP
  ,repeat_interval => 'FREQ=SECONDLY;INTERVAL=5'
  ,end_date        => NULL
  ,job_class       => 'DEFAULT_JOB_CLASS'
  ,job_type        => 'PLSQL_BLOCK'
  ,job_action      => 'insert into sch_test values (s1.nextval,s2.nextval);'
  ,enabled         => TRUE
);

Named schedule objects and named program objects are only useful if you have several of them running in complex combinations and dependencies.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download