Oracle PL/SQL/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.

Demonstrates DBMS_APPLICATION_INFO and its two procedures.

 
SQL> DECLARE
  2
  3    my_env VARCHAR2(1);
  4
  5  BEGIN
  6
  7    
  8    DBMS_APPLICATION_INFO.READ_CLIENT_INFO(my_env);
  9
 10    
 11    DBMS_OUTPUT.PUT_LINE("Read CLIENT_INFO value ["||my_env||"].");
 12
 13    
 14    DBMS_OUTPUT.PUT_LINE("Set CLIENT_INFO to     [1].");
 15
 16    
 17    DBMS_APPLICATION_INFO.SET_CLIENT_INFO("1");
 18
 19    
 20    DBMS_APPLICATION_INFO.READ_CLIENT_INFO(my_env);
 21
 22    
 23    DBMS_OUTPUT.PUT_LINE("Read CLIENT_INFO value ["||my_env||"].");
 24
 25  END;
 26  /
Read CLIENT_INFO value [].
Set CLIENT_INFO to     [1].
Read CLIENT_INFO value [1].
PL/SQL procedure successfully completed.
SQL>

Execute the DBMS_APPLICATION_INFO package.

 
SQL> EXECUTE DBMS_APPLICATION_INFO.SET_CLIENT_INFO("1");
PL/SQL procedure successfully completed.
SQL>

Use DBMS_APPLICATION_INFO and USERENV() in building a publish-and-subscribe implementation.

 
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> SELECT   USERENV("CLIENT_INFO")
  2  FROM     dual;
USERENV("CLIENT_INFO")
----------------------------------------------------------------
1
1 row selected.
SQL>
SQL> 
SQL> EXECUTE DBMS_APPLICATION_INFO.SET_CLIENT_INFO("1");
PL/SQL procedure successfully completed.
SQL>
SQL> 
SQL> SELECT   USERENV("CLIENT_INFO")
  2  FROM     dual;
USERENV("CLIENT_INFO")
----------------------------------------------------------------
1
1 row selected.
SQL>
SQL>
SQL>