Oracle PL/SQL/System Packages/dbms job

DBMS_JOB package.

   
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL> CREATE SEQUENCE temp_seq
  2    START WITH 1
  3    INCREMENT BY 1;
Sequence created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE TempInsert AS
  2  BEGIN
  3    INSERT INTO MyTable (num_col, char_col)
  4      VALUES (temp_seq.NEXTVAL,
  5              TO_CHAR(SYSDATE, "DD-MON-YYYY HH24:MI:SS"));
  6    COMMIT;
  7  END TempInsert;
  8  /
Procedure created.
SQL>
SQL> VARIABLE v_JobNum NUMBER
SQL> BEGIN
  2    DBMS_JOB.SUBMIT(:v_JobNum, "TempInsert;", SYSDATE,
  3                    "SYSDATE + (90/(24*60*60))");
  4    COMMIT;
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL> PRINT v_JobNum
  V_JOBNUM
----------
        22
SQL>
SQL> BEGIN
  2    DBMS_JOB.REMOVE(:v_JobNum);
  3    COMMIT;
  4  END;
  5  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> DROP SEQUENCE temp_seq;
Sequence dropped.
SQL>
SQL> drop table mytable;
Table dropped.

how to submit TempInsert as a job.

   
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL> CREATE SEQUENCE temp_seq
  2    START WITH 1
  3    INCREMENT BY 1;
Sequence created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE TempInsert AS
  2  BEGIN
  3    INSERT INTO MyTable (num_col, char_col)
  4      VALUES (1,
  5              TO_CHAR(SYSDATE, "DD-MON-YYYY HH24:MI:SS"));
  6    COMMIT;
  7  END TempInsert;
  8  /
Procedure created.
SQL>
SQL> VARIABLE v_JobNum NUMBER
SQL>
SQL> BEGIN
  2    DBMS_JOB.SUBMIT(:v_JobNum, "TempInsert;", SYSDATE,
  3                    "sysdate + (90/(24*60*60))");
  4    COMMIT;
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL> PRINT v_JobNum
  V_JOBNUM
----------
        46
SQL>
SQL> DROP SEQUENCE temp_seq;
Sequence dropped.
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
SQL>

Remove a procedure from the queue after 5 executions.

   
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE TempInsert
  2    (p_NextDate IN OUT DATE) AS
  3    v_SeqNum   NUMBER;
  4    v_StartNum NUMBER;
  5    v_SQLErr   VARCHAR2(60);
  6  BEGIN
  7    SELECT 1
  8      INTO v_SeqNum
  9      FROM dual;
 10
 11    BEGIN
 12      SELECT num_col
 13        INTO v_StartNum
 14        FROM MyTable
 15        WHERE char_col = "TempInsert Start";
 16
 17      INSERT INTO MyTable (num_col, char_col)
 18        VALUES (v_SeqNum,
 19                TO_CHAR(SYSDATE, "DD-MON-YYYY HH24:MI:SS"));
 20
 21    EXCEPTION
 22      WHEN NO_DATA_FOUND THEN
 23        DELETE FROM MyTable;
 24
 25        INSERT INTO MyTable (num_col, char_col)
 26          VALUES (v_SeqNum, "TempInsert Start");
 27    END;
 28
 29    IF v_SeqNum - V_StartNum > 5 THEN
 30      p_NextDate := NULL;
 31      INSERT INTO MyTable (num_col, char_col)
 32        VALUES (v_SeqNum, "TempInsert End");
 33
 34    END IF;
 35
 36    COMMIT;
 37  EXCEPTION
 38    WHEN OTHERS THEN
 39      v_SQLErr := SUBSTR(SQLERRM, 1, 60);
 40      INSERT INTO MyTable (num_col, char_col)
 41        VALUES (1, v_SQLErr);
 42
 43      p_NextDate := NULL;
 44
 45      COMMIT;
 46  END TempInsert;
 47  /
Procedure created.
SQL>
SQL> VARIABLE v_JobNum NUMBER
SQL> BEGIN
  2    DBMS_JOB.SUBMIT(:v_JobNum, "TempInsert(next_date);", SYSDATE,
  3                    "sysdate + (60/(24*60*60))");
  4    COMMIT;
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL> PRINT v_JobNum
  V_JOBNUM
----------
        45
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>

Simple Procedure with SUBMIT

   
SQL> --
SQL>
SQL> CREATE OR REPLACE PROCEDURE HELLO AS
  2  BEGIN
  3      DBMS_OUTPUT.PUT_LINE ("Hello World! " || TO_CHAR(SYSDATE,"MM-DD-YY HH:MM:SS AM"));
  4  END;
  5  /
Procedure created.
SQL>
SQL>
SQL> DECLARE
  2       v_JobNum  BINARY_INTEGER;
  3  BEGIN
  4        DBMS_JOB.SUBMIT(v_JobNum,"HELLO;",SYSDATE, "SYSDATE + (1/(24*60*60))");
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL> --

Submit job to change password

    
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.

SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.

SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.

SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.

SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.

SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.

SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.

SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.

SQL>
SQL> create table PASSWORD_AUDIT (empno number,date_change date );

SQL>
SQL> create or replace procedure admin.reset_password(id varchar2) is
  2   v_username varchar2(30);
  3   v_job number;
  4   begin
  5       select ename into v_username from emp where empno = id and mgr = ( select empno from emp where ename = user );
  6       insert into password_audit values (id, user, sysdate);
  7       dbms_job.submit(v_job,"begin execute immediate " ||"""alter user ""||v_username||"" "||" identified by "||v_username||"""; end;");
  8   exception
  9   when no_data_found then
 10       raise_application_error(-20000, "You are not authorised to alter emp "||id);
 11   end;
 12  /

SQL> drop table emp;
Table dropped.

Use dbms_job.submit to call "execute immediate"

  
SQL> create table myTable (
  2   tname varchar2(30),
  3   cname varchar2(30),
  4   changed date
  5  );
Table created.
SQL>
SQL> create or replace procedure RUN_DDL(m varchar2) is
  2   pragma autonomous_transaction;
  3   begin
  4   execute immediate m;
  5   end;
  6  /
SQL>
SQL> create or replace procedure ADD_COLUMN(p_table varchar2, p_column varchar2) is
  2   v number;
  3   j number;
  4   begin
  5       insert into myTable values (p_table, p_column, sysdate);
  6       dbms_job.submit(j, "run_ddl(""alter table "||p_table||" add "||p_column||""");");
  7   end;
  8  /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.

Using DBMS_OUTPUT to See the Assigned Job Number

   
SQL> --
SQL> DECLARE
  2       v_jobnum BINARY_INTEGER;
  3  BEGIN
  4       DBMS_JOB.SUBMIT(v_jobnum,"HELLO;",SYSDATE,
  5            "SYSDATE + (1/(24*60*60))");
  6       DBMS_OUTPUT.ENABLE;
  7       DBMS_OUTPUT.PUT_LINE("Your Job Number assigned is: " || v_jobnum);
  8  END;
  9  /
Your Job Number assigned is: 42
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --

Using the SUBMIT Procedure

   
SQL>
SQL> DECLARE
  2   v_job NUMBER;
  3   v_what VARCHAR2(1000) := "insert into test_table values (9999);";
  4   BEGIN
  5   dbms_job.submit(job => v_job,
  6                   what => v_what,
  7                   next_date => sysdate,
  8                   interval => "sysdate + 1/24");
  9    COMMIT;
 10    END;
 11   /

SQL>