Utilities Library
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
General PL/SQL Utilities Library
Supporting Tables
CREATE SEQUENCE event_seq;

CREATE TABLE util_config (
pname       VARCHAR2(30) NOT NULL,
pvalue      VARCHAR2(30) NOT NULL,
active_flag VARCHAR2(1)  DEFAULT 'A' NOT NULL)
PCTUSED 99 PCTFREE 0;

CREATE TABLE util_event_log (
instance_id      NUMBER(2),
run_no           NUMBER,
schema_name      VARCHAR2(30),
package_name     VARCHAR2(30),
object_name      VARCHAR2(30),
object_type      VARCHAR2(19),
line_number      NUMBER,
beg_date         TIMESTAMP(6),
end_date         TIMESTAMP(6),
host_name        VARCHAR2(64),
instance_name    VARCHAR2(16),
active_instances NUMBER(3),
service_name     VARCHAR2(30),
module_name      VARCHAR2(48),
action_name      VARCHAR2(32),
client_info      VARCHAR2(64),
severity         NUMBER(2),
sql_errno        NUMBER(5),
event_text       VARCHAR2(256),
log_comment      VARCHAR2(256))
PARTITION BY RANGE (instance_id)
INTERVAL (1) (
PARTITION root_par VALUES LESS THAN (2))
PCTUSED 99 PCTFREE 0;
Constants
CREATE OR REPLACE PACKAGE mlib_constants AUTHID CURRENT_USER IS
/*******************************************************************************
* NAME: mlib_constants                                                         *
* TYPE: Package Specification                                                  *
*                                                                              *
* PURPOSE: General purpose utilities                                           *
*                                                                              *
* Revision History:                                                            *
********************************************************************************
* Date Author Comments                                                         *
* 01-AUG-2011 DA Morgan Initial release                                        *
*                                                                              *
* Define data types and constants to be used, universally, through-out the     *
* customized portions of this project.                                         *
*                                                                              *
* Copyright (c) 2011 Daniel A. Morgan                                          *
* Made Available Pursuant To GNU General Public License 2'                     *
*******************************************************************************/

/*********** GLOBAL CONSTANTS *************/
c_objVer CONSTANT VARCHAR2(6) := '2.0.1';

-- display formatting
gc_ExpDateFormat CONSTANT VARCHAR2(21) := 'MM-DD-YYYY HH24:MI:SS'; -- expended date format
gc_RegDateFormat CONSTANT VARCHAR2(10) := 'MM-DD-YYYY'; -- regular date format
gc_StrLimit CONSTANT PLS_INTEGER := 4000;

/* -- TYPE DEFINITIONS -- */
SUBTYPE ecode IS VARCHAR2(6);
SUBTYPE emesg IS VARCHAR2(120);

/* -- Exceptions Log
-00000, 'Non Error Condition: Progress/Status Report'

/* -- Severity -- */
gc_LS_NoLogging PLS_INTEGER := 0;
gc_LS_Debug     PLS_INTEGER := 1;
gc_LS_Detailed  PLS_INTEGER := 2;
gc_LS_Info      PLS_INTEGER := 3;
gc_LS_Warning   PLS_INTEGER := 4;
gc_LS_Critical  PLS_INTEGER := 5;
END mlib_constants;
/
sho err
Package Header
CREATE OR REPLACE PACKAGE mlib_utils AUTHID CURRENT_USER IS
/*******************************************************************************
* NAME: mlib_utils                                                             *
* TYPE: Package Specification                                                  *
*                                                                              *
* PURPOSE: General purpose utilities                                           *
*                                                                              *
* Revision History:                                                            *
********************************************************************************
* Date Author Comments                                                         *
* 01-AUG-2011 DA Morgan Initial release                                        *
*                                                                              *
* This code is protected under applicable copyright law and may be freely      *
* used and distributed as long as in so doing it remains free. Any             *
* modifications to this code do not constitute a waiver of this restriction.   *
*                                                                              *
* Copyright (c) 2011 Daniel A. Morgan                                          *
* Made Available Pursuant To GNU General Public License 2'                     *
*******************************************************************************/

/*********** GLOBAL CONSTANTS *************/
c_objVer CONSTANT VARCHAR2(6) := '2.0.1';

/*********** GLOBAL VARIABLES *************/
g_errMesg mlib_constants.emesg;
g_errNum  mlib_constants.ecode;
/*********** PUBLIC FUNCTIONS *************/
FUNCTION Get_Action RETURN VARCHAR2;
FUNCTION Get_ClientInfo RETURN VARCHAR2;
FUNCTION Get_Module RETURN VARCHAR2;
FUNCTION Get_SQLError_Text RETURN VARCHAR2;
FUNCTION Get_Version RETURN VARCHAR2;

/*********** PUBLIC PROCEDURES ************/
PROCEDURE Build_Exceptions_Table(
 pSchema_name IN user_users.username%TYPE,
 pTable_Name  IN user_tables.table_name%TYPE);

PROCEDURE Change_Config(
 pName  IN util_config.pname%TYPE,
 pValue IN util_config.pvalue%TYPE);

PROCEDURE Drop_PK_Not_Null_Check_Cons;

PROCEDURE Log_Error(
 pRunNo    IN util_event_log.run_no%TYPE,
 pEndDate  IN util_event_log.end_date%TYPE DEFAULT SYSDATE,
 pSeverity IN util_event_log.severity%TYPE DEFAULT NULL,
 pErrNum   IN util_event_log.sql_errno%TYPE,
 pEvtTxt   IN util_event_log.log_comment%TYPE);

PROCEDURE Log_Event_End (
 pRunNo      IN util_event_log.run_no%TYPE,
 pEndDate    IN util_event_log.end_date%TYPE DEFAULT SYSDATE,
 pSeverity   IN util_event_log.severity%TYPE DEFAULT NULL,
 pLogComment IN util_event_log.log_comment%TYPE DEFAULT NULL);

PROCEDURE Log_Event_Start(
 pRunNo      IN util_event_log.run_no%TYPE,
 pBegDate    IN util_event_log.beg_date%TYPE DEFAULT SYSDATE,
 pSeverity   IN util_event_log.severity%TYPE DEFAULT NULL,
 pLogComment IN util_event_log.log_comment%TYPE DEFAULT NULL);

PROCEDURE Reset_Action;
PROCEDURE Reset_ClientInfo;
PROCEDURE Reset_Module;
PROCEDURE Set_Action(pAction_Name IN VARCHAR2);
PROCEDURE Set_ClientInfo(pClientInfo IN VARCHAR2);

PROCEDURE Set_Module(
 pModuleName IN VARCHAR2,
 pActionName IN VARCHAR2 DEFAULT NULL);
END mlib_utils;
/
sho err
Package Body
CREATE OR REPLACE PACKAGE BODY mlib_utils IS
/*******************************************************************************
* NAME: mlib_utils                                                             *
* TYPE: Package Body                                                           *
*                                                                              *
* PURPOSE: General purpose utilities                                           *
*                                                                              *
* Revision History:                                                            *
********************************************************************************
* Date Author Comments                                                         *
* 01-AUG-2011 DA Morgan Initial release                                        *
*                                                                              *
* This code is protected under applicable copyright law and may be freely      *
* used and distributed as long as in so doing it remains free. Any             *
* modifications to this code do not constitute a waiver of this restriction.   *
*                                                                              *
* Copyright (c) 2011 Daniel A. Morgan                                          *
* Made Available Pursuant To GNU General Public License 2'                     *
*******************************************************************************/

FUNCTION get_version RETURN VARCHAR2 IS
BEGIN
  RETURN c_objVer;
END get_version;

--==============================================================
PROCEDURE Build_Exceptions_Table(
 pSchema_name IN user_users.username%TYPE,
 pTable_Name  IN user_tables.table_name%TYPE)
IS
 ErrTabExists EXCEPTION;
 PRAGMA EXCEPTION_INIT(ErrTabExists, -00955);
BEGIN
  dbms_errlog.create_error_log(pTable_Name);
EXCEPTION
  WHEN ErrTabExists THEN
    g_errNum := sqlcode;
    g_errMesg := sqlerrm;
    RAISE_APPLICATION_ERROR(-20001, 'Failure Creating Exception Table For ' || pTable_Name);
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002, 'MLIB_UTILS.BUILD_EXCEPTIONS_TABLE: Fatal Procedure Error Logged');
END Build_Exceptions_Table;
--==============================================================
PROCEDURE Change_Config(pName IN util_config.pname%TYPE, pValue IN util_config.pvalue%TYPE) IS
BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE util_config READ WRITE';

  UPDATE /* mlib_utils.change_config01 */ util_config uc
  SET uc.pvalue = pValue
  WHERE uc.pname = pName;
  COMMIT;

  EXECUTE IMMEDIATE 'ALTER TABLE util_config READ WRITE'; -- change to read only later
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20003, 'Invalid Parameters: Please Verify All Values Are Valid');
END;
--==============================================================
PROCEDURE Drop_PK_Not_Null_Check_Cons IS
vPKTabName user_constraints.table_name%TYPE;
vPKColName user_cons_columns.column_name%TYPE;

CURSOR pcur IS
SELECT /* mlib_utils.change_config02 */ uc.table_name, ucc.column_name
FROM user_constraints uc, user_cons_columns ucc
WHERE uc.constraint_name = ucc.constraint_name
AND constraint_type = 'P'
AND uc.table_name NOT LIKE '%$%';

CURSOR ccur IS
SELECT  /* mlib_utils.change_config03 */ uc.constraint_name
FROM user_constraints uc, user_cons_columns ucc
WHERE uc.constraint_name = ucc.constraint_name
AND constraint_type = 'C'
AND uc.table_name = vPKTabName
AND ucc.column_name = vPKColName;

FUNCTION get_search_condition(p_cons_name IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
 l_search_con user_constraints.search_condition%TYPE;
 BEGIN
   SELECT  /* mlib_utils.change_config04 */ search_condition into l_search_con
   FROM user_constraints
   WHERE constraint_name = p_cons_name;
   RETURN l_search_con;
 END;
BEGIN
  FOR prec IN pcur LOOP
    vPKTabName := prec.table_name;
    vPKColName := prec.column_name;

    FOR crec IN ccur LOOP
      IF UPPER(get_search_condition(crec.constraint_name)) LIKE '%IS NOT NULL' THEN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || vPKTabName || ' DROP CONSTRAINT ' || crec.constraint_name;
      END IF;
    END LOOP;
  END LOOP;
EXCEPTION
  WHEN no_data_found THEN
    RAISE;
  WHEN others THEN
    RAISE;
END Drop_PK_Not_Null_Check_Cons;
--==============================================================
FUNCTION Get_ClientInfo RETURN VARCHAR2 IS
BEGIN
  RETURN sys_context('USERENV', 'CLIENT_INFO');
END Get_ClientInfo;
--==============================================================
FUNCTION Get_Module RETURN VARCHAR2 IS
  vRetVal VARCHAR2(81) := sys_context('USERENV', 'MODULE') || ',' || sys_context('USERENV', 'ACTION');
BEGIN
  IF vRetval = ',' THEN
    vRetval := NULL;
  END IF;
  RETURN vRetval;
END Get_Module;
--==============================================================
FUNCTION Get_Action RETURN VARCHAR2 IS
 vRetVal VARCHAR2(32) := sys_context('USERENV', 'ACTION');
BEGIN
  RETURN vRetval;
END Get_Action;
--==============================================================
FUNCTION Get_SQLError_Text RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_utility.Format_Error_Stack ||
         dbms_utility.Format_error_backtrace ||
         dbms_utility.Format_call_Stack;
END get_SQLError_Text;
--==============================================================
PROCEDURE Initialize IS
BEGIN
  NULL;
END Initialize;
--==============================================================
PROCEDURE Log_Event_Start(
 pRunNo      IN util_event_log.run_no%TYPE,
 pBegDate    IN util_event_log.beg_date%TYPE DEFAULT SYSDATE,
 pSeverity   IN util_event_log.severity%TYPE DEFAULT NULL,
 pLogComment IN util_event_log.log_comment%TYPE)
IS
 cInstID   CONSTANT util_event_log.instance_id%TYPE := sys_context('USERENV', 'INSTANCE');
 cInstName CONSTANT util_event_log.instance_name%TYPE := sys_context('USERENV', 'INSTANCE_NAME');
 cHostName CONSTANT util_event_log.host_name%TYPE := sys_context('USERENV', 'SERVER_HOST');
 cModName  CONSTANT util_event_log.module_name%TYPE := sys_context('USERENV', 'MODULE');
 cActName  CONSTANT util_event_log.action_name%TYPE := sys_context('USERENV', 'ACTION');
 cCliInfo  CONSTANT util_event_log.client_info%TYPE := sys_context('USERENV', 'CLIENT_INFO');
 cServName CONSTANT util_event_log.service_name%TYPE := sys_context('USERENV', 'SERVICE_NAME');

 vInstCount  util_event_log.active_instances%TYPE := 1; -- for RAC use later
 vInstTab    dbms_utility.instance_table;
 vLineNumber all_source.line%TYPE;
 vObjType    all_objects.object_type%TYPE;
 vPkgName    all_objects.object_name%TYPE;
 vSchemaName all_objects.owner%TYPE;

 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  owa_util.who_called_me(vSchemaName, vPkgName, vLineNumber, vObjType);

  IF dbms_utility.is_cluster_database THEN
    dbms_utility.active_instances(vInstTab, vInstCount);
  END IF;

  INSERT /* mlib_utils.change_config05 */ INTO util_event_log
  (instance_id, run_no, schema_name, package_name, object_type, line_number,
  beg_date, host_name, instance_name, active_instances, service_name,
  module_name, action_name, client_info, severity, log_comment)
  VALUES
  (cInstID, pRunNo, vSchemaName, vPkgName, vObjType, vLineNumber,
  pBegDate, cHostName, cInstName, vInstCount, cServName,
  cModName, cActName, cCliInfo, pSeverity, pLogComment);

  COMMIT;
  -- this procedure intentionally does not contain exception handling: do not add one.
END Log_Event_Start;

--==============================================================
PROCEDURE Log_Event_End(
 pRunNo      IN util_event_log.run_no%TYPE,
 pEndDate    IN util_event_log.end_date%TYPE DEFAULT SYSDATE,
 pSeverity   IN util_event_log.severity%TYPE DEFAULT NULL,
 pLogComment IN util_event_log.log_comment%TYPE DEFAULT NULL) IS

PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE /* mlib_utils.change_config06 */ util_event_log
  SET end_date = pEndDate,
      severity = pSeverity,
      log_comment = pLogComment
  WHERE run_no = pRunNo;
  COMMIT;
  -- this procedure intentionally does not contain exception handling: do not add one.
END Log_Event_end;

--==============================================================
PROCEDURE Log_Error(
 pRunNo    IN util_event_log.run_no%TYPE,
 pEndDate  IN util_event_log.end_date%TYPE DEFAULT SYSDATE,
 pSeverity IN util_event_log.severity%TYPE DEFAULT NULL,
 pErrNum   IN util_event_log.sql_errno%TYPE,
 pEvtTxt   IN util_event_log.log_comment%TYPE) IS

 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE /* mlib_utils.change_config07 */ util_event_log
  SET end_date = pEndDate,
      severity = pSeverity,
      sql_errno = pErrNum,
      event_text = pEvtTxt
  WHERE run_no = pRunNo;
  COMMIT;
  -- this procedure intentionally does not contain exception handling: do not add one.
END Log_Error;
--==============================================================
PROCEDURE Reset_Action IS
BEGIN
  dbms_application_info.set_action(NULL);
END Reset_Action;
--==============================================================
PROCEDURE Reset_ClientInfo IS
BEGIN
  dbms_application_info.set_client_info('-');
END Reset_ClientInfo;
--==============================================================
PROCEDURE Reset_Module IS
BEGIN
  dbms_application_info.set_module(NULL, NULL);
END Reset_Module;
--==============================================================
PROCEDURE Set_Action(pAction_Name IN VARCHAR2) IS
BEGIN
  dbms_application_info.set_action(pAction_Name);
END Set_Action;
--==============================================================
PROCEDURE Set_ClientInfo (pClientInfo IN VARCHAR2) IS
BEGIN
  dbms_application_info.set_client_info(SUBSTR(pClientInfo,1,64));
END Set_ClientInfo;
--==============================================================
PROCEDURE Set_Module(pModuleName IN VARCHAR2, pActionName IN VARCHAR2 DEFAULT NULL) IS
BEGIN
  dbms_application_info.set_module(SUBSTR(pModuleName,1,48), SUBSTR(pActionName,1,32));
END Set_Module;
--==============================================================
BEGIN
  initialize;
END mlib_utils;
/
sho err
...
 
Related Topics
DBMS_STATS
Sequences
Tables
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved