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 |
| ... |
|
