Oracle PL/SQL Tutorial/System Packages/dbms application info
Содержание
- 1 dbms_application_info.set_client_info
- 2 dbms_application_info.set_client_info and dbms_application_info.set_action
- 3 dbms_application_info.set_client_info to change client information
- 4 DBMS_APPLICATION_INFO.SET_MODULE
- 5 desc v$session_longops
- 6 Read Changed Values and Display
- 7 Read Original Values and Display
dbms_application_info.set_client_info
<source lang="sql">
SQL> SQL> create table myTable as select rownum id, a.* from all_objects a where rownum < 20; Table created. SQL> SQL> SQL> declare
2 myLong long; 3 myNumber number; 4 begin 5 dbms_alert.register( "WAITING" ); 6 for i in 1 .. 999 loop 7 dbms_application_info.set_client_info( i ); 8 dbms_alert.waitone( "WAITING", myLong, myNumber, 0 ); 9 exit when myNumber = 0; 10 for x in ( select * from myTable order by 1, 2, 3, 4 ) 11 loop 12 null; 13 end loop; 14 end loop; 15 end; 16 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table myTable; Table dropped.</source>
dbms_application_info.set_client_info and dbms_application_info.set_action
<source lang="sql">
SQL> SQL> declare
2 l_owner varchar2(30) default "SYS"; 3 l_cnt number default 0; 4 begin 5 dbms_application_info.set_client_info( "owner="||l_owner ); 6 7 for x in ( select * from all_objects where owner = l_owner ) 8 loop 9 l_cnt := l_cnt+1; 10 dbms_application_info.set_action( "processing row " || l_cnt ); 11 end loop; 12 end; 13 /
PL/SQL procedure successfully completed.</source>
dbms_application_info.set_client_info to change client information
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> create or replace view emp_view as select ename, empno from emp
2 where ename = sys_context( "userenv", "client_info");
View created. SQL> SQL> exec dbms_application_info.set_client_info("BLAKE"); PL/SQL procedure successfully completed. SQL> SQL> select * from emp_view; ENAME EMPNO
----------
BLAKE 7698 SQL> SQL> drop table emp; Table dropped.</source>
DBMS_APPLICATION_INFO.SET_MODULE
<source lang="sql">
SQL> SQL> CREATE TABLE employee
2 (employee_id NUMBER(7), 3 last_name VARCHAR2(25), 4 first_name VARCHAR2(25), 5 userid VARCHAR2(8), 6 start_date DATE, 7 comments VARCHAR2(255), 8 manager_id NUMBER(7), 9 title VARCHAR2(25), 10 department_id NUMBER(7), 11 salary NUMBER(11, 2), 12 commission_pct NUMBER(4, 2) 13 );
Table created. SQL> SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL); 1 row created. SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL); 1 row created. SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL); 1 row created. SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL); 1 row created. SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL); 1 row created. SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL); 1 row created. SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL); 1 row created. SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL); 1 row created. SQL> SQL> SQL> DECLARE
2 CURSOR cur_emp_sal IS 3 SELECT ROWID 4 FROM employee 5 WHERE salary > 1100; 6 lv_counter_num PLS_INTEGER := 0; 7 BEGIN 8 FOR cur_emp_sal_rec IN cur_emp_sal LOOP 9 lv_counter_num := lv_counter_num + 1; 10 UPDATE employee 11 SET salary = salary + 10000 12 WHERE ROWID = cur_emp_sal_rec.ROWID; 13 IF MOD(lv_counter_num,500) = 0 THEN 14 DBMS_APPLICATION_INFO.SET_MODULE(lv_counter_num, NULL); 15 COMMIT; 16 END IF; 17 END LOOP; 18 DBMS_APPLICATION_INFO.SET_MODULE(lv_counter_num, NULL); 19 END; 20 /
PL/SQL procedure successfully completed. SQL> SQL> drop table employee; Table dropped.</source>
desc v$session_longops
<source lang="sql">
SQL> SQL> declare
2 l_nohint number default dbms_application_info.set_session_longops_nohint; 3 l_rindex number default l_nohint; 4 l_slno number; 5 begin 6 for i in 1 .. 25 7 loop 8 dbms_lock.sleep(2); 9 dbms_application_info.set_session_longops 10 ( rindex => l_rindex, 11 slno => l_slno, 12 op_name => "my long running operation", 13 target => 9999, 14 target_desc => "my target", 15 context => 0, 16 sofar => i, 17 totalwork => 25, 18 units => "loops" 19 ); 20 end loop; 21 end; 22 /
PL/SQL procedure successfully completed.</source>
Read Changed Values and Display
<source lang="sql">
SQL> SQL> SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> DECLARE
2 lv_module_txt VARCHAR2(48); 3 lv_action_txt VARCHAR2(32); 4 BEGIN 5 DBMS_APPLICATION_INFO.READ_MODULE(lv_module_txt, lv_action_txt); 6 DBMS_OUTPUT.PUT_LINE("After Module: " || lv_module_txt || 7 CHR(9) || " Action: " || lv_action_txt); 8 END; 9 /
After Module: PL/SQL BLock Action: Testing DBMS_APPLICATION_INFO PL/SQL procedure successfully completed.</source>
Read Original Values and Display
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> DECLARE
2 lv_module_txt VARCHAR2(48); 3 lv_action_txt VARCHAR2(32); 4 BEGIN 5 6 DBMS_APPLICATION_INFO.READ_MODULE(lv_module_txt, lv_action_txt); 7 DBMS_OUTPUT.PUT_LINE("Before Module: " || lv_module_txt || 8 CHR(9) || " Action: " || lv_action_txt); 9 DBMS_APPLICATION_INFO.SET_MODULE("PL/SQL BLock", 10 "Testing DBMS_APPLICATION_INFO"); 11 END; 12 /
Before Module: 9 Action: PL/SQL procedure successfully completed.</source>