DBMS_JOB demonstration : DBMS_JOB « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. DBMS_JOB

SQL>
SQL> CREATE TABLE run_table (message VARCHAR2(40));

Table created.

SQL>
SQL> -- create a procedure to insert rows into the test table:
SQL> CREATE OR REPLACE PROCEDURE p_run_insert IS
  2  BEGIN
  3    INSERT INTO run_table VALUES ('Execution at ' ||  to_char(sysdate, 'dd-mon-yy hh:mi:ss') ) ;
  4    COMMIT ;
  5  END;
  6  /

Procedure created.

SQL>
SQL> -- schedule that procedure to run every 10 seconds (approximately):
SQL> VARIABLE p_jobno number
SQL> BEGIN
  2    DBMS_JOB.SUBMIT (:p_jobno,'P_RUN_INSERT;', SYSDATE,  'SYSDATE + (10/(24*60*60))');
  3    COMMIT;  /* must commit after SUBMIT */
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> PRINT p_jobno

   P_JOBNO
----------
       122

SQL>
SQL>
SQL> exec dbms_job.remove(1)
BEGIN dbms_job.remove(1); END;

*
ERROR at line 1:
ORA-23421: job number 1 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 171
ORA-06512: at line 1


SQL>
SQL> drop table run_table;

Table dropped.

SQL>
SQL>