Oracle PL/SQL/System Packages/dbms application info

Материал из SQL эксперт
Перейти к: навигация, поиск

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>
   
  


Demonstrates DBMS_APPLICATION_INFO and its two procedures.

   <source lang="sql">

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>

 </source>
   
  


Execute the DBMS_APPLICATION_INFO package.

   <source lang="sql">

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

 </source>
   
  


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

   <source lang="sql">

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>

 </source>