SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
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)
4 VALUES (temp_seq.nextval);
5 COMMIT;
6 END TempInsert;
7 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE TempInsert
2 (p_NextDate IN OUT DATE) AS
3 v_CurrVal NUMBER;
4 BEGIN
5 INSERT INTO MyTable (num_col)
6 VALUES (temp_seq.nextval);
7 SELECT temp_seq.currval
8 INTO v_CurrVal
9 FROM dual;
10 IF v_CurrVal > 15 THEN
11 p_NextDate := NULL;
12 END IF;
13 COMMIT;
14 END TempInsert;
15 /
Procedure created.
SQL>
SQL> VARIABLE v_JobNum NUMBER
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:v_JobNum, 'TempInsert(next_date);', sysdate,
3 'sysdate + (1/(24*60*60))');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> PRINT v_JobNum
V_JOBNUM
----------
42
SQL>
SQL>
SQL> drop SEQUENCE temp_seq;
Sequence dropped.
SQL> drop table MyTable;
Table dropped.
SQL>
|