job_creation

CREATE OR REPLACE PROCEDURE Insert_scheduler

is

BEGIN
    Insert into dummy values ('R-'||to_char(sysdate,'DD-MON-YY HH24:MI:SS'));
    Insert into dummy values ('A-'||to_char(sysdate,'DD-MON-YY HH24:MI:SS'));
    Insert into dummy values ('M-'||to_char(sysdate,'DD-MON-YY HH24:MI:SS'));
    commit;
EXCEPTION
WHEN OTHERS THEN
Insert into dummy values ('');
END;
/
declare
alp varchar2(200);
begin
Insert_scheduler;
DBMS_OUTPUT.put_line('Completed');
end;
/
truncate table dummy;
/
select * from dummy;
/
select * from ALL_SCHEDULER_JOBS;
/
select * from all_objects where OBJECT_NAME like 'INSERT_SCHEDULER%';
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             =>  'INSERT_SCHEDULER_JOB',
   job_type             =>  'STORED_PROCEDURE',
   job_action           =>  'Apps.INSERT_SCHEDULER',
   start_date           =>  '22-AUG-08 02.00.00.000000000 PM ASIA/CALCUTTA',
   repeat_interval      =>  'FREQ=DAILY;BYHOUR=0;BYMINUTE=1', /* every minute */
   end_date             =>  '22-AUG-08 08.00.00.000000000 PM ASIA/CALCUTTA',
   auto_drop            =>   TRUE,
--   job_class            =>  'batch_update_jobs',
   comments             =>  'My new job');
END;
/
declare
alp varchar2(200);
BEGIN
  DBMS_SCHEDULER.RUN_JOB(
    JOB_NAME            => 'INSERT_SCHEDULER_JOB',
    USE_CURRENT_SESSION => True);
END;
/
BEGIN
  DBMS_SCHEDULER.STOP_JOB('INSERT_SCHEDULER_JOB');
END;
/
BEGIN
  DBMS_SCHEDULER.DROP_JOB(
     job_name         => 'INSERT_SCHEDULER_JOB',
     defer            => TRUE,
     commit_semantics => 'TRANSACTIONAL');
END;
/

set define off;
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'APPS.INSERT_SCHEDULER;'
     ,next_date  => to_date('08-22-2018 14:44:14','mm/dd/yyyy hh24:mi:ss')
     ,interval   => 'SYSDATE+1/1440'
     ,no_parse   => TRUE
    );
DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
commit;
END;
/
select to_char(sysdate,'DD-MON-YY HH24:MI:SS'),to_char(SYSDATE+1/1440,'DD-MON-YY HH24:MI:SS') from dual;
/
SELECT r.sid, r.job, r.this_date, r.this_sec, what
FROM dba_jobs_running r,dba_jobs j
WHERE r.job = j.job
AND j.broken ='N';
/
BEGIN
 DBMS_JOB.broken
  ( job       =>4  -- this is the job number assigned above
   ,broken  => TRUE
  );
 COMMIT;
END;