Oracle PL/SQL Tutorial/System Packages/dbms application info

dbms_application_info.set_client_info

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.

dbms_application_info.set_client_info and dbms_application_info.set_action

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.

dbms_application_info.set_client_info to change client information

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.

DBMS_APPLICATION_INFO.SET_MODULE

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.

desc v$session_longops

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.

Read Changed Values and Display

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.

Read Original Values and Display

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.