Oracle PL/SQL/System Packages/dbms application info
Содержание
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>