Oracle Built−in Packages- P79: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk IS . IF display_TF AND current_module IS NOT NULL OR current_client_info IS NOT NULL THEN . END set_stats Using the register_app Package The following SQL Plus script demonstrates how the register_app package can be used to register each step of a multistep batch process. The script displays the resource utilization statistics that have been collected for each step. While the script is executing DBAs can monitor which step is currently running by querying V SESSION for the session executing the script. Filename on companion disk rem rem rem rem SQL Plus script to demonstrate the use of package rem REGISTER_APP for tracking performance statistics rem rem set serveroutput on size 100000 set feedback off rem rem register module first with display OFF to rem initialize stats then set display ON rem execute FALSE execute execute TRUE set feedback on rem rem create a table my_dictionary copied from dictionary rem execute CREATE CREATE TABLE my_dictionary id table_name comments TABLESPACE user_data2 AS SELECT rownum A. FROM dictionary A rem rem update one third of my_dictionary rows rem execute UPDATE UPDATE my_dictionary SET comments RPAD comments 2000 WHERE MOD id 3 0 rem rem delete one third of my_dictionary rows rem execute DELETE DELETE FROM my_dictionary WHERE MOD id 3 1 Using the register_app Package 381 Appendix A What s on the Companion Disk rem rem drop table my_dictionary rem execute DROP DROP TABLE my_dictionary rem rem unregister and display previous step stats rem execute null null Here is sample output generated by the script SQL @regtest Module Action BEGIN Client Info Stats elapsed secs .15 physical reads 0 logical reads 0 PL SQL procedure successfully completed. Table created. .
