tailieunhanh - Oracle Built−in Packages- P110

Oracle Built−in Packages- P110: 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 END close_links There are a few things to note in this procedure. First exceptions are declared and assigned to the two Oracle errors that can be raised by the procedure. This is done using PRAGMA EXCEPTION_INIT compiler directives. Next comes a loop through all database links available to the user. For each link in the loop we execute in a block and trap the exceptions raised by links that were not open or in use. Trapping the exceptions allows the loop to continue until all links have been processed. Originally the procedure would close only links that were not in use. I decided to enhance it to accept a BOOLEAN parameter called force_with_commit_TF. When this parameter is TRUE the dblink_in_use exception handler issues a COMMIT. This terminates the current transaction and frees all database links to be closed including the one that raised the exception that is closed in the exception handler. This enhancement allows the procedure to close all database links for the session. NOTE The V DBLINK virtual table shows database links that the current session has open. The IN_TRANSACTION column indicates whether the open link is in use or can be closed using . The set_sql_trace procedure The SQL trace facility is an invaluable tool for debugging application performance problems. However one problem that developers and DBAs often run into when using SQL trace is identifying the correct trace file from among the possibly hundreds of trace files that tend to collect and hang around in the directory specified by the USER_DUMP_DEST parameter. One technique is to put a literal tag in trace files by executing a SQL command such as the following SELECT JOHN B TRACE 1 FROM DUAL When issued immediately after setting SQL_TRACE to TRUE the statement will appear in the trace file and a utility like grep or awk can be used to .