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> 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>