Mega Code Archive
Remove a procedure from the queue after 5 executions
<![CDATA[
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>
]]>