tailieunhanh - Oracle Built−in Packages- P109

Oracle Built−in Packages- P109: 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 SET_LABEL SET_MLS_LABEL_FORMAT SET_NLS SET_ROLE These programs are high-level in that they would normally be called directly from an application program and not be buried deep inside layers of PL SQL code. In fact the SET_ROLE procedure can only be called from anonymous PL SQL blocks and not from within stored program procedures and functions code. So in practice an application would begin by prompting the user for preferences issue the appropriate procedure calls and then move on to the real work. Other programs in DBMS_SESSION are geared toward manipulating session-level resource utilization particularly memory. In this category are the following DBMS_SESSION programs SET_CLOSE_CACHED_OPEN_CURSORS CLOSE_DATABASE_LINK FREE_UNUSED_USER_MEMORY RESET_PACKAGE These are also quite high-level routines but more likely to find their way into application code under the right circumstances. One thing that DBMS_SESSION does not have and that it should is a function to return the current session id. This is frequently asked for by developers and DBAs and is relatively easy to provide. NOTE The source code for all of the examples is in a file called which creates the package called my_session shown in this section that includes these examples. Adding value to DBMS_SESSION Let s take a look at how we can use DBMS_SESSION and add a little value along the way. I ve created a package called my_session to do just that. Here is the package specification Filename on companion disk CREATE OR REPLACE PACKAGE my_session Extends some of the functionality of DBMS_SESSION and provides access to additional session-level information. Author John Beresniewicz Savant Corp 12 22 97 exposed load_unique_id as per Phil Pitha 09 07 97 modified function SID to assert WNPS and not call load_my_session_rec 07 27 97 created Compilation Requirements SELECT on session SELECT on sesstat SELECT on