Remove a procedure from the queue after 5 executions. : dbms_job « System Packages « Oracle PL / 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>

   
    
  








Related examples in the same category

1.Simple Procedure with SUBMIT
2.Using DBMS_OUTPUT to See the Assigned Job Number
3.Using the SUBMIT Procedure
4.DBMS_JOB package.
5.Use dbms_job.submit to call 'execute immediate'
6.how to submit TempInsert as a job.
7.Submit job to change password