Oracle PL/SQL/System Packages/dbms job
Содержание
- 1 DBMS_JOB package.
- 2 how to submit TempInsert as a job.
- 3 Remove a procedure from the queue after 5 executions.
- 4 Simple Procedure with SUBMIT
- 5 Submit job to change password
- 6 Use dbms_job.submit to call "execute immediate"
- 7 Using DBMS_OUTPUT to See the Assigned Job Number
- 8 Using the SUBMIT Procedure
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>