Oracle Built-in Packages

DBMS_APPLICATION_INFO Examples

Oracle suggests that one way to extend DBMS_APPLICATION_INFO is to capture session performance statistics as part of the process of registering modules and actions. To demonstrate how this might be done, I have created a package called register_app.

About the register_app Package

The programs in register_app are very similar to those in DBMS_APPLICATION_INFO. Here is the package specification:

/* Filename on companion disk: register.sql */* CREATE OR REPLACE PACKAGE register_app IS /* || Enhances DBMS_APPLICATION_INFO by capturing performance || statistics when module, action, or client_info are set. || || Statistics may be displayed in SQL*Plus for tracking and || debugging purposes. A useful enhancement would be to || extend this idea to a logging feature, so stats are logged || to a table for analysis. || || Also enforces requirement that a module be registered before || an action can be registered. || || Author: John Beresniewicz, Savant Corp || Created: 09/01/97 || || Compilation Requirements: || || SELECT on SYS.V_$MYSTAT || SELECT on SYS.V_$STATNAME || || Execution Requirements: || || */ /* registers the application module */ PROCEDURE module (module_name_IN IN VARCHAR2 ,action_name_IN IN VARCHAR2 DEFAULT 'BEGIN'); /* registers the action within module */ PROCEDURE action(action_name_IN IN VARCHAR2); /* registers additional application client information */ PROCEDURE client_info(client_info_IN IN VARCHAR2); /* returns the currently registered ...