Oracle Schedule Jobs Creation

Topic Introduction: When we want a task or code will be automatically executed in our database on a certain time, we use Schedule Job on the database. It is the most important work to synchronize data or task completion automatically on the database. It can be used for Data synchronization of attendance machines, Database Backup, etc.

Oracle Schedule Jobs Creation.

1. Table creation for testing Scheduler Job:

CREATE TABLE TEST_SCHEDULER

(

SCH_ID NUMBER,

SCH_DATE DATE,

SCH_REMARKS VATCHAR2(30)

);

2. Create Scheduler Job:

BEGIN

DBMS_SCHEDULER.create_job (

  job_name => 'test_schjob_full',

  job_type => 'PLSQL_BLOCK',

  job_action => 'DECLATE

V_COUNT NUMBER :=0;

BEGIN

  SELECT COUNT(*) INTO V_COUNT FROM TEST_SCHEDULER;

  INSERT INTO TEST_SCHEDULER (SCH_ID,SCH_DATE,SCH_REMARKS)

  VALUES (V_COUNT+1,SYSDATE,NULL);

END;',

  start_date => SYSTIMESTAMP,

  repeat_interval => 'freq=SECOUNDLY; interval=5',

  end_date => NULL,

  enabled => TRUE,

  comments => 'My test scheduler job full');

END;

/

3. SQL Query for Test Schedule Job Result:

SELECT * FROM  TEST_SCHEDULER;