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.
<source lang="sql">
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.
</source>
how to submit TempInsert as a job.
<source lang="sql">
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>
</source>
Remove a procedure from the queue after 5 executions.
<source lang="sql">
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>
</source>
Simple Procedure with SUBMIT
<source lang="sql">
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> --
</source>
Submit job to change password
<source lang="sql">
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.
</source>
Use dbms_job.submit to call "execute immediate"
<source lang="sql">
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.
</source>
Using DBMS_OUTPUT to See the Assigned Job Number
<source lang="sql">
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> --
</source>
Using the SUBMIT Procedure
<source lang="sql">
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>
</source>
