612 lines
31 KiB
Plaintext
612 lines
31 KiB
Plaintext
create or replace PACKAGE CT_MRDS.ENV_MANAGER
|
|
AUTHID CURRENT_USER
|
|
AS
|
|
/**
|
|
* General comment for package: Please put comments for functions and procedures as shown in below example.
|
|
* It is a standard.
|
|
* The structure of comment is used by GET_PACKAGE_DOCUMENTATION function
|
|
* which returns documentation text for confluence page (to Copy-Paste it).
|
|
**/
|
|
|
|
-- Example comment:
|
|
/**
|
|
* @name EX_PROCEDURE_NAME
|
|
* @desc Procedure description
|
|
* @example select ENV_MANAGER.EX_PROCEDURE_NAME(pParameter => 129) from dual;
|
|
* @ex_rslt Example Result
|
|
**/
|
|
|
|
-- Package Version Information (Semantic Versioning: MAJOR.MINOR.PATCH)
|
|
PACKAGE_VERSION CONSTANT VARCHAR2(10) := '3.1.0';
|
|
PACKAGE_BUILD_DATE CONSTANT VARCHAR2(20) := '2025-10-22 20:57:00';
|
|
PACKAGE_AUTHOR CONSTANT VARCHAR2(100) := 'Grzegorz Michalski';
|
|
|
|
-- Version History (Latest changes first)
|
|
VERSION_HISTORY CONSTANT VARCHAR2(4000) :=
|
|
'3.1.0 (2025-10-22): Added package hash tracking and automatic change detection system (SHA256 hashing)' || CHR(13)||CHR(10) ||
|
|
'3.0.0 (2025-10-22): Added package versioning system with centralized version management functions' || CHR(13)||CHR(10) ||
|
|
'2.1.0 (2025-10-15): Added ANALYZE_VALIDATION_ERRORS function for comprehensive CSV validation analysis' || CHR(13)||CHR(10) ||
|
|
'2.0.0 (2025-10-01): Added LOG_PROCESS_ERROR procedure with enhanced error diagnostics and stack traces' || CHR(13)||CHR(10) ||
|
|
'1.5.0 (2025-09-20): Added console logging support with gvConsoleLoggingEnabled configuration' || CHR(13)||CHR(10) ||
|
|
'1.0.0 (2025-09-01): Initial release with error management and configuration system';
|
|
|
|
TYPE Error_Record IS RECORD (
|
|
code PLS_INTEGER,
|
|
message VARCHAR2(4000)
|
|
);
|
|
|
|
TYPE tErrorList IS TABLE OF Error_Record INDEX BY PLS_INTEGER;
|
|
|
|
Errors tErrorList;
|
|
|
|
|
|
guid VARCHAR2(32);
|
|
gvEnv VARCHAR2(200);
|
|
gvUsername VARCHAR2(128);
|
|
gvOsuser VARCHAR2(128);
|
|
gvMachine VARCHAR2(64);
|
|
gvModule VARCHAR2(64);
|
|
|
|
gvNameSpace VARCHAR2(200);
|
|
gvRegion VARCHAR2(200);
|
|
gvDataBucketName VARCHAR2(200);
|
|
gvInboxBucketName VARCHAR2(200);
|
|
gvArchiveBucketName VARCHAR2(200);
|
|
gvDataBucketUri VARCHAR2(200);
|
|
gvInboxBucketUri VARCHAR2(200);
|
|
gvArchiveBucketUri VARCHAR2(200);
|
|
gvCredentialName VARCHAR2(200);
|
|
|
|
-- Overwritten by variable "LoggingEnabled" in A_FILE_MANAGER_CONFIG.CONFIG_VARIABLE table
|
|
gvLoggingEnabled VARCHAR2(3) := 'ON'; -- 'ON' or 'OFF'
|
|
|
|
-- Overwritten by variable "MinLogLevel" in A_FILE_MANAGER_CONFIG.CONFIG_VARIABLE table
|
|
-- Possible values: DEBUG ,INFO ,WARNING ,ERROR
|
|
gvMinLogLevel VARCHAR2(10) := 'DEBUG';
|
|
|
|
-- Overwritten by variable "DefaultDateFormat" in A_FILE_MANAGER_CONFIG.CONFIG_VARIABLE table
|
|
gvDefaultDateFormat VARCHAR2(200) := 'DD/MM/YYYY HH24:MI:SS';
|
|
|
|
-- Overwritten by variable "ConsoleLoggingEnabled" in A_FILE_MANAGER_CONFIG.CONFIG_VARIABLE table
|
|
gvConsoleLoggingEnabled VARCHAR2(3) := 'ON'; -- 'ON' or 'OFF'
|
|
|
|
cgBL CONSTANT VARCHAR2(2) := CHR(13)||CHR(10);
|
|
|
|
vgSourceFileConfigKey PLS_INTEGER;
|
|
|
|
vgMsgTmp VARCHAR2(32000);
|
|
--Exceptions
|
|
ERR_EMPTY_FILEURI_AND_RECKEY EXCEPTION;
|
|
CODE_EMPTY_FILEURI_AND_RECKEY CONSTANT PLS_INTEGER := -20001;
|
|
MSG_EMPTY_FILEURI_AND_RECKEY VARCHAR2(4000) := 'Either pFileUri or pSourceFileReceivedKey must be not null';
|
|
PRAGMA EXCEPTION_INIT( ERR_EMPTY_FILEURI_AND_RECKEY
|
|
,CODE_EMPTY_FILEURI_AND_RECKEY);
|
|
|
|
|
|
ERR_NO_CONFIG_MATCH_FOR_FILEURI EXCEPTION;
|
|
CODE_NO_CONFIG_MATCH_FOR_FILEURI CONSTANT PLS_INTEGER := -20002;
|
|
MSG_NO_CONFIG_MATCH_FOR_FILEURI VARCHAR2(4000) := 'No match for source file in A_SOURCE_FILE_CONFIG table'
|
|
||cgBL||' The file provided in parameter: pFileUri does not have '
|
|
||cgBL||' coresponding configuration in A_SOURCE_FILE_CONFIG table';
|
|
PRAGMA EXCEPTION_INIT( ERR_NO_CONFIG_MATCH_FOR_FILEURI
|
|
,CODE_NO_CONFIG_MATCH_FOR_FILEURI);
|
|
|
|
ERR_MULTIPLE_MATCH_FOR_SRCFILE EXCEPTION;
|
|
CODE_MULTIPLE_MATCH_FOR_SRCFILE CONSTANT PLS_INTEGER := -20003;
|
|
MSG_MULTIPLE_MATCH_FOR_SRCFILE VARCHAR2(4000) := 'Multiple match for source file in A_SOURCE_FILE_CONFIG table';
|
|
PRAGMA EXCEPTION_INIT( ERR_MULTIPLE_MATCH_FOR_SRCFILE
|
|
,CODE_MULTIPLE_MATCH_FOR_SRCFILE);
|
|
|
|
ERR_MISSING_COLUMN_DATE_FORMAT EXCEPTION;
|
|
CODE_MISSING_COLUMN_DATE_FORMAT CONSTANT PLS_INTEGER := -20004;
|
|
MSG_MISSING_COLUMN_DATE_FORMAT VARCHAR2(4000) := 'Missing entry in config table: A_COLUMN_DATE_FORMAT primary key(TEMPLATE_TABLE_NAME, COLUMN_NAME)'
|
|
||cgBL||' Remember: each column which data_type IN (''DATE'', ''TIMESTAMP'')'
|
|
||cgBL||' should have DateFormat specified in A_COLUMN_DATE_FORMAT table '
|
|
||cgBL||' for example: ''YYYY-MM-DD''';
|
|
PRAGMA EXCEPTION_INIT( ERR_MISSING_COLUMN_DATE_FORMAT
|
|
,CODE_MISSING_COLUMN_DATE_FORMAT);
|
|
|
|
ERR_MULTIPLE_COLUMN_DATE_FORMAT EXCEPTION;
|
|
CODE_MULTIPLE_COLUMN_DATE_FORMAT CONSTANT PLS_INTEGER := -20005;
|
|
MSG_MULTIPLE_COLUMN_DATE_FORMAT VARCHAR2(4000) := 'Multiple records for date format in A_COLUMN_DATE_FORMAT table'
|
|
||cgBL||' There should be only one format specified for each DAT/TIMESTAMP column';
|
|
PRAGMA EXCEPTION_INIT( ERR_MULTIPLE_COLUMN_DATE_FORMAT
|
|
,CODE_MULTIPLE_COLUMN_DATE_FORMAT);
|
|
|
|
|
|
ERR_DIDNT_GET_LOAD_OPERATION_ID EXCEPTION;
|
|
CODE_DIDNT_GET_LOAD_OPERATION_ID CONSTANT PLS_INTEGER := -20006;
|
|
MSG_DIDNT_GET_LOAD_OPERATION_ID VARCHAR2(4000) := 'Didnt get load operation id from external table validation';
|
|
PRAGMA EXCEPTION_INIT( ERR_DIDNT_GET_LOAD_OPERATION_ID
|
|
,CODE_DIDNT_GET_LOAD_OPERATION_ID);
|
|
|
|
ERR_NO_CONFIG_FOR_RECEIVED_FILE EXCEPTION;
|
|
CODE_NO_CONFIG_FOR_RECEIVED_FILE CONSTANT PLS_INTEGER := -20007;
|
|
MSG_NO_CONFIG_FOR_RECEIVED_FILE VARCHAR2(4000) := 'No match for received source file in A_SOURCE_FILE_CONFIG '
|
|
||cgBL||' or missing data in A_SOURCE_FILE_RECEIVED table for provided pSourceFileReceivedKey parameter';
|
|
PRAGMA EXCEPTION_INIT( ERR_NO_CONFIG_FOR_RECEIVED_FILE
|
|
,CODE_NO_CONFIG_FOR_RECEIVED_FILE);
|
|
|
|
ERR_MULTI_CONFIG_FOR_RECEIVED_FILE EXCEPTION;
|
|
CODE_MULTI_CONFIG_FOR_RECEIVED_FILE CONSTANT PLS_INTEGER := -20008;
|
|
MSG_MULTI_CONFIG_FOR_RECEIVED_FILE VARCHAR2(4000) := 'Multiple matchs for received source file in A_SOURCE_FILE_CONFIG';
|
|
PRAGMA EXCEPTION_INIT( ERR_MULTI_CONFIG_FOR_RECEIVED_FILE
|
|
,CODE_MULTI_CONFIG_FOR_RECEIVED_FILE);
|
|
|
|
ERR_FILE_NOT_FOUND_ON_CLOUD EXCEPTION;
|
|
CODE_FILE_NOT_FOUND_ON_CLOUD CONSTANT PLS_INTEGER := -20009;
|
|
MSG_FILE_NOT_FOUND_ON_CLOUD VARCHAR2(4000) := 'File not found on the cloud';
|
|
PRAGMA EXCEPTION_INIT( ERR_FILE_NOT_FOUND_ON_CLOUD
|
|
,CODE_FILE_NOT_FOUND_ON_CLOUD);
|
|
|
|
ERR_FILE_VALIDATION_FAILED EXCEPTION;
|
|
CODE_FILE_VALIDATION_FAILED CONSTANT PLS_INTEGER := -20010;
|
|
MSG_FILE_VALIDATION_FAILED VARCHAR2(4000) := 'File validation failed';
|
|
PRAGMA EXCEPTION_INIT( ERR_FILE_VALIDATION_FAILED
|
|
,CODE_FILE_VALIDATION_FAILED);
|
|
|
|
ERR_EXCESS_COLUMNS_DETECTED EXCEPTION;
|
|
CODE_EXCESS_COLUMNS_DETECTED CONSTANT PLS_INTEGER := -20011;
|
|
MSG_EXCESS_COLUMNS_DETECTED VARCHAR2(4000) := 'CSV file contains more columns than template allows';
|
|
PRAGMA EXCEPTION_INIT( ERR_EXCESS_COLUMNS_DETECTED
|
|
,CODE_EXCESS_COLUMNS_DETECTED);
|
|
|
|
ERR_NO_CONFIG_MATCH EXCEPTION;
|
|
CODE_NO_CONFIG_MATCH CONSTANT PLS_INTEGER := -20012;
|
|
MSG_NO_CONFIG_MATCH VARCHAR2(4000) := 'No match for specified parameters in A_SOURCE_FILE_CONFIG table';
|
|
PRAGMA EXCEPTION_INIT( ERR_NO_CONFIG_MATCH
|
|
,CODE_NO_CONFIG_MATCH);
|
|
|
|
ERR_UNKNOWN_PREFIX EXCEPTION;
|
|
CODE_UNKNOWN_PREFIX CONSTANT PLS_INTEGER := -20013;
|
|
MSG_UNKNOWN_PREFIX VARCHAR2(4000) := 'Unknown prefix';
|
|
PRAGMA EXCEPTION_INIT( ERR_UNKNOWN_PREFIX
|
|
,CODE_UNKNOWN_PREFIX);
|
|
|
|
ERR_TABLE_NOT_EXISTS EXCEPTION;
|
|
CODE_TABLE_NOT_EXISTS CONSTANT PLS_INTEGER := -20014;
|
|
MSG_TABLE_NOT_EXISTS VARCHAR2(4000) := 'Table does not exist';
|
|
PRAGMA EXCEPTION_INIT( ERR_TABLE_NOT_EXISTS
|
|
,CODE_TABLE_NOT_EXISTS);
|
|
|
|
ERR_COLUMN_NOT_EXISTS EXCEPTION;
|
|
CODE_COLUMN_NOT_EXISTS CONSTANT PLS_INTEGER := -20015;
|
|
MSG_COLUMN_NOT_EXISTS VARCHAR2(4000) := 'Column does not exist in table';
|
|
PRAGMA EXCEPTION_INIT( ERR_COLUMN_NOT_EXISTS
|
|
,CODE_COLUMN_NOT_EXISTS);
|
|
|
|
ERR_UNSUPPORTED_DATA_TYPE EXCEPTION;
|
|
CODE_UNSUPPORTED_DATA_TYPE CONSTANT PLS_INTEGER := -20016;
|
|
MSG_UNSUPPORTED_DATA_TYPE VARCHAR2(4000) := 'Unsupported data type';
|
|
PRAGMA EXCEPTION_INIT( ERR_UNSUPPORTED_DATA_TYPE
|
|
,CODE_UNSUPPORTED_DATA_TYPE);
|
|
|
|
ERR_MISSING_SOURCE_KEY EXCEPTION;
|
|
CODE_MISSING_SOURCE_KEY CONSTANT PLS_INTEGER := -20017;
|
|
MSG_MISSING_SOURCE_KEY VARCHAR2(4000) := 'The Source was not found in parent table A_SOURCE';
|
|
PRAGMA EXCEPTION_INIT( ERR_MISSING_SOURCE_KEY
|
|
,CODE_MISSING_SOURCE_KEY);
|
|
|
|
ERR_NULL_SOURCE_FILE_CONFIG_KEY EXCEPTION;
|
|
CODE_NULL_SOURCE_FILE_CONFIG_KEY CONSTANT PLS_INTEGER := -20018;
|
|
MSG_NULL_SOURCE_FILE_CONFIG_KEY VARCHAR2(4000) := 'No entry in A_SOURCE_FILE_CONFIG table for specified A_SOURCE_FILE_CONFIG_KEY';
|
|
PRAGMA EXCEPTION_INIT( ERR_NULL_SOURCE_FILE_CONFIG_KEY
|
|
,CODE_NULL_SOURCE_FILE_CONFIG_KEY);
|
|
|
|
ERR_DUPLICATED_SOURCE_KEY EXCEPTION;
|
|
CODE_DUPLICATED_SOURCE_KEY CONSTANT PLS_INTEGER := -20019;
|
|
MSG_DUPLICATED_SOURCE_KEY VARCHAR2(4000) := 'The Source already exists in the A_SOURCE table';
|
|
PRAGMA EXCEPTION_INIT( ERR_DUPLICATED_SOURCE_KEY
|
|
,CODE_DUPLICATED_SOURCE_KEY);
|
|
|
|
ERR_MISSING_CONTAINER_CONFIG EXCEPTION;
|
|
CODE_MISSING_CONTAINER_CONFIG CONSTANT PLS_INTEGER := -20020;
|
|
MSG_MISSING_CONTAINER_CONFIG VARCHAR2(4000) := 'No match in A_SOURCE_FILE_CONFIG table where SOURCE_FILE_TYPE=''CONTAINER'' and specified SOURCE_FILE_ID';
|
|
PRAGMA EXCEPTION_INIT( ERR_MISSING_CONTAINER_CONFIG
|
|
,CODE_MISSING_CONTAINER_CONFIG);
|
|
|
|
ERR_MULTIPLE_CONTAINER_ENTRIES EXCEPTION;
|
|
CODE_MULTIPLE_CONTAINER_ENTRIES CONSTANT PLS_INTEGER := -20021;
|
|
MSG_MULTIPLE_CONTAINER_ENTRIES VARCHAR2(4000) := 'Multiple matches in A_SOURCE_FILE_CONFIG table where SOURCE_FILE_TYPE=''CONTAINER'' and specified SOURCE_FILE_ID';
|
|
PRAGMA EXCEPTION_INIT( ERR_MULTIPLE_CONTAINER_ENTRIES
|
|
,CODE_MULTIPLE_CONTAINER_ENTRIES);
|
|
|
|
ERR_WRONG_DESTINATION_PARAM EXCEPTION;
|
|
CODE_WRONG_DESTINATION_PARAM CONSTANT PLS_INTEGER := -20022;
|
|
MSG_WRONG_DESTINATION_PARAM VARCHAR2(4000) := 'Wrong destination parameter provided.';
|
|
PRAGMA EXCEPTION_INIT( ERR_WRONG_DESTINATION_PARAM
|
|
,CODE_WRONG_DESTINATION_PARAM);
|
|
|
|
ERR_FILE_NOT_EXISTS_ON_CLOUD EXCEPTION;
|
|
CODE_FILE_NOT_EXISTS_ON_CLOUD CONSTANT PLS_INTEGER := -20023;
|
|
MSG_FILE_NOT_EXISTS_ON_CLOUD VARCHAR2(4000) := 'File not exists on cloud.';
|
|
PRAGMA EXCEPTION_INIT( ERR_FILE_NOT_EXISTS_ON_CLOUD
|
|
,CODE_FILE_NOT_EXISTS_ON_CLOUD);
|
|
|
|
ERR_FILE_ALREADY_REGISTERED EXCEPTION;
|
|
CODE_FILE_ALREADY_REGISTERED CONSTANT PLS_INTEGER := -20024;
|
|
MSG_FILE_ALREADY_REGISTERED VARCHAR2(4000) := 'File already registered in A_SOURCE_FILE_RECEIVED table.';
|
|
PRAGMA EXCEPTION_INIT( ERR_FILE_ALREADY_REGISTERED
|
|
,CODE_FILE_ALREADY_REGISTERED);
|
|
|
|
ERR_WRONG_DATE_TIMESTAMP_FORMAT EXCEPTION;
|
|
CODE_WRONG_DATE_TIMESTAMP_FORMAT CONSTANT PLS_INTEGER := -20025;
|
|
MSG_WRONG_DATE_TIMESTAMP_FORMAT VARCHAR2(4000) := 'Provided DATE or TIMESTAMP format has errors (possible duplicated codes, ex: ''DD'').';
|
|
PRAGMA EXCEPTION_INIT( ERR_WRONG_DATE_TIMESTAMP_FORMAT
|
|
,CODE_WRONG_DATE_TIMESTAMP_FORMAT);
|
|
|
|
ERR_ENVIRONMENT_NOT_SET EXCEPTION;
|
|
CODE_ENVIRONMENT_NOT_SET CONSTANT PLS_INTEGER := -20026;
|
|
MSG_ENVIRONMENT_NOT_SET VARCHAR2(4000) := 'EnvironmentID not set'
|
|
||cgBL||' Information about environment is needed to get proper configuration values.'
|
|
||cgBL||' It can be set up in two different ways:'
|
|
||cgBL||' 1. Set it on session level: execute DBMS_SESSION.SET_IDENTIFIER (client_id => ''dev'')'
|
|
||cgBL||' 2. Set it on configuration level: Insert into CT_MRDS.A_FILE_MANAGER_CONFIG (ENVIRONMENT_ID,CONFIG_VARIABLE,CONFIG_VARIABLE_VALUE) values (''default'',''environment_id'',''dev'')'
|
|
||cgBL||' Session level setup (1.) takes precedence over configuration level one (2.)'
|
|
;
|
|
PRAGMA EXCEPTION_INIT( ERR_ENVIRONMENT_NOT_SET
|
|
,CODE_ENVIRONMENT_NOT_SET);
|
|
|
|
|
|
ERR_CONFIG_VARIABLE_NOT_SET EXCEPTION;
|
|
CODE_CONFIG_VARIABLE_NOT_SET CONSTANT PLS_INTEGER := -20027;
|
|
MSG_CONFIG_VARIABLE_NOT_SET VARCHAR2(4000) := 'Missing configuration value in A_FILE_MANAGER_CONFIG';
|
|
PRAGMA EXCEPTION_INIT( ERR_CONFIG_VARIABLE_NOT_SET
|
|
,CODE_CONFIG_VARIABLE_NOT_SET);
|
|
|
|
ERR_NOT_INPUT_SOURCE_FILE_TYPE EXCEPTION;
|
|
CODE_NOT_INPUT_SOURCE_FILE_TYPE CONSTANT PLS_INTEGER := -20028;
|
|
MSG_NOT_INPUT_SOURCE_FILE_TYPE VARCHAR2(4000) := 'Archival can be executed only for A_SOURCE_FILE_CONFIG_KEY where SOURCE_FILE_TYPE=''INPUT''';
|
|
PRAGMA EXCEPTION_INIT( ERR_NOT_INPUT_SOURCE_FILE_TYPE
|
|
,CODE_NOT_INPUT_SOURCE_FILE_TYPE);
|
|
|
|
ERR_EXP_DATA_FOR_ARCH_FAILED EXCEPTION;
|
|
CODE_EXP_DATA_FOR_ARCH_FAILED CONSTANT PLS_INTEGER := -20029;
|
|
MSG_EXP_DATA_FOR_ARCH_FAILED VARCHAR2(4000) := 'Export data for archival failed.';
|
|
PRAGMA EXCEPTION_INIT( ERR_EXP_DATA_FOR_ARCH_FAILED
|
|
,CODE_EXP_DATA_FOR_ARCH_FAILED);
|
|
|
|
ERR_RESTORE_FILE_FROM_TRASH EXCEPTION;
|
|
CODE_RESTORE_FILE_FROM_TRASH CONSTANT PLS_INTEGER := -20030;
|
|
MSG_RESTORE_FILE_FROM_TRASH VARCHAR2(4000) := 'Unexpected issues occured while archival process. Restoration of exported files failed.';
|
|
PRAGMA EXCEPTION_INIT( ERR_RESTORE_FILE_FROM_TRASH
|
|
,CODE_RESTORE_FILE_FROM_TRASH);
|
|
|
|
ERR_CHANGE_STAT_TO_ARCHIVED_FAILED EXCEPTION;
|
|
CODE_CHANGE_STAT_TO_ARCHIVED_FAILED CONSTANT PLS_INTEGER := -20031;
|
|
MSG_CHANGE_STAT_TO_ARCHIVED_FAILED VARCHAR2(4000) := 'Failed to change file status to: ARCHIVED in A_SOURCE_FILE_RECEIVED table.';
|
|
PRAGMA EXCEPTION_INIT( ERR_CHANGE_STAT_TO_ARCHIVED_FAILED
|
|
,CODE_CHANGE_STAT_TO_ARCHIVED_FAILED);
|
|
|
|
ERR_MOVE_FILE_TO_TRASH_FAILED EXCEPTION;
|
|
CODE_MOVE_FILE_TO_TRASH_FAILED CONSTANT PLS_INTEGER := -20032;
|
|
MSG_MOVE_FILE_TO_TRASH_FAILED VARCHAR2(4000) := 'FAILED to move file to TRASH before DROPPING it.';
|
|
PRAGMA EXCEPTION_INIT( ERR_MOVE_FILE_TO_TRASH_FAILED
|
|
,CODE_MOVE_FILE_TO_TRASH_FAILED);
|
|
|
|
ERR_DROP_EXPORTED_FILES_FAILED EXCEPTION;
|
|
CODE_DROP_EXPORTED_FILES_FAILED CONSTANT PLS_INTEGER := -20033;
|
|
MSG_DROP_EXPORTED_FILES_FAILED VARCHAR2(4000) := 'FAILED to move file to TRASH before DROPPING it.';
|
|
PRAGMA EXCEPTION_INIT( ERR_DROP_EXPORTED_FILES_FAILED
|
|
,CODE_DROP_EXPORTED_FILES_FAILED);
|
|
|
|
ERR_INVALID_BUCKET_AREA EXCEPTION;
|
|
CODE_INVALID_BUCKET_AREA CONSTANT PLS_INTEGER := -20034;
|
|
MSG_INVALID_BUCKET_AREA VARCHAR2(4000) := 'Invalid bucket area specified. Valid values: INBOX, ODS, DATA, ARCHIVE';
|
|
PRAGMA EXCEPTION_INIT( ERR_INVALID_BUCKET_AREA
|
|
,CODE_INVALID_BUCKET_AREA);
|
|
|
|
ERR_UNKNOWN EXCEPTION;
|
|
CODE_UNKNOWN CONSTANT PLS_INTEGER := -20999;
|
|
MSG_UNKNOWN VARCHAR2(4000) := 'Unknown Error Occured';
|
|
PRAGMA EXCEPTION_INIT( ERR_UNKNOWN
|
|
,CODE_UNKNOWN);
|
|
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
|
|
|
|
|
|
|
|
/**
|
|
* @name LOG_PROCESS_EVENT
|
|
* @desc Insert a new log record into A_PROCESS_LOG table.
|
|
* Also outputs to console if gvConsoleLoggingEnabled = 'ON'.
|
|
* Respects logging level configuration (gvMinLogLevel).
|
|
* @example ENV_MANAGER.LOG_PROCESS_EVENT('Process completed successfully', 'INFO', 'pParam1=value1');
|
|
* @ex_rslt Record inserted into A_PROCESS_LOG table and optionally displayed in console output
|
|
**/
|
|
PROCEDURE LOG_PROCESS_EVENT (
|
|
pLogMessage VARCHAR2
|
|
,pLogLevel VARCHAR2 DEFAULT 'ERROR'
|
|
,pParameters VARCHAR2 DEFAULT NULL
|
|
,pProcessName VARCHAR2 DEFAULT 'FILE_MANAGER'
|
|
);
|
|
|
|
/**
|
|
* @name LOG_PROCESS_ERROR
|
|
* @desc Insert a detailed error record into A_PROCESS_LOG table with full stack trace, backtrace, and call stack.
|
|
* This procedure captures comprehensive error information for debugging purposes while
|
|
* allowing clean user-facing error messages to be raised separately.
|
|
* @param pLogMessage - Base error message description
|
|
* @param pParameters - Procedure parameters for context
|
|
* @param pProcessName - Name of the calling process/package
|
|
* @ex_rslt Record inserted into A_PROCESS_LOG table with complete error stack information
|
|
*/
|
|
PROCEDURE LOG_PROCESS_ERROR (
|
|
pLogMessage VARCHAR2
|
|
,pParameters VARCHAR2 DEFAULT NULL
|
|
,pProcessName VARCHAR2 DEFAULT 'FILE_MANAGER'
|
|
);
|
|
|
|
/**
|
|
* @name INIT_ERRORS
|
|
* @desc Loads data into Errors array.
|
|
* Errors array is a list of Record(Error_Code, Error_Message) index by Error_Code.
|
|
* Called automatically during package initialization.
|
|
* @example Called automatically when package is first referenced
|
|
* @ex_rslt Errors array populated with all error codes and messages
|
|
**/
|
|
PROCEDURE INIT_ERRORS;
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_DEFAULT_ENV
|
|
* @desc It returns string with name of default environment.
|
|
* Return string is A_FILE_MANAGER_CONFIG.ENVIRONMENT_ID value.
|
|
* @example select ENV_MANAGER.GET_DEFAULT_ENV() from dual;
|
|
* @ex_rslt dev
|
|
**/
|
|
FUNCTION GET_DEFAULT_ENV
|
|
RETURN VARCHAR2;
|
|
|
|
|
|
|
|
/**
|
|
* @name INIT_VARIABLES
|
|
* @desc For specified pEnv parameter (A_FILE_MANAGER_CONFIG.ENVIRONMENT_ID)
|
|
* Assign values to following global package variables:
|
|
* - gvNameSpace
|
|
* - gvRegion
|
|
* - gvCredentialName
|
|
* - gvInboxBucketName
|
|
* - gvDataBucketName
|
|
* - gvArchiveBucketName
|
|
* - gvInboxBucketUri
|
|
* - gvDataBucketUri
|
|
* - gvArchiveBucketUri
|
|
* - gvLoggingEnabled
|
|
* - gvMinLogLevel
|
|
* - gvDefaultDateFormat
|
|
* - gvConsoleLoggingEnabled
|
|
**/
|
|
PROCEDURE INIT_VARIABLES(
|
|
pEnv VARCHAR2
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_ERROR_MESSAGE
|
|
* @desc It returns string with error message for specified pCode (Error_Code).
|
|
* Error message is take from Errors Array loaded by INIT_ERRORS procedure
|
|
* @example select ENV_MANAGER.GET_ERROR_MESSAGE(pCode => -20009) from dual;
|
|
* @ex_rslt File not found on the cloud
|
|
**/
|
|
FUNCTION GET_ERROR_MESSAGE(
|
|
pCode PLS_INTEGER
|
|
) RETURN VARCHAR2;
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_ERROR_STACK
|
|
* @desc It returns string with all possible error stack info.
|
|
* Error message is take from Errors Array loaded by INIT_ERRORS procedure
|
|
* @example
|
|
* select ENV_MANAGER.GET_ERROR_STACK(
|
|
* pFormat => 'OUTPUT'
|
|
* ,pCode => -20009
|
|
* ,pSourceFileReceivedKey => NULL)
|
|
* from dual
|
|
* @ex_rslt
|
|
* ------------------------------------------------------+
|
|
* Error Message:
|
|
* ORA-0000: normal, successful completion
|
|
* -------------------------------------------------------
|
|
* Error Stack:
|
|
* -------------------------------------------------------
|
|
* Error Backtrace:
|
|
* ------------------------------------------------------+
|
|
**/
|
|
FUNCTION GET_ERROR_STACK(
|
|
pFormat VARCHAR2
|
|
,pCode PLS_INTEGER
|
|
,pSourceFileReceivedKey CT_MRDS.A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY%TYPE DEFAULT NULL
|
|
) RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name FORMAT_PARAMETERS
|
|
* @desc Formats parameter list for logging purposes.
|
|
* Converts SYS.ODCIVARCHAR2LIST to formatted string with proper NULL handling.
|
|
* @example select ENV_MANAGER.FORMAT_PARAMETERS(SYS.ODCIVARCHAR2LIST('param1=value1', 'param2=NULL')) from dual;
|
|
* @ex_rslt param1=value1 ,
|
|
* param2=NULL
|
|
**/
|
|
FUNCTION FORMAT_PARAMETERS(
|
|
pParameterList SYS.ODCIVARCHAR2LIST
|
|
) RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name ANALYZE_VALIDATION_ERRORS
|
|
* @desc Analyzes CSV validation errors and generates detailed diagnostic report.
|
|
* Compares CSV structure with template table and provides specific error analysis.
|
|
* Includes suggested solutions for common validation issues.
|
|
* @param pValidationLogTable - Name of validation log table (e.g., VALIDATE$242_LOG)
|
|
* @param pTemplateSchema - Schema of template table (e.g., CT_ET_TEMPLATES)
|
|
* @param pTemplateTable - Name of template table (e.g., MOCK_PROC_TABLE)
|
|
* @param pCsvFileUri - URI of CSV file being validated
|
|
* @example SELECT ENV_MANAGER.ANALYZE_VALIDATION_ERRORS('VALIDATE$242_LOG', 'CT_ET_TEMPLATES', 'MOCK_PROC_TABLE', 'https://...') FROM DUAL;
|
|
* @ex_rslt Detailed validation analysis report with column mismatches and solutions
|
|
**/
|
|
FUNCTION ANALYZE_VALIDATION_ERRORS(
|
|
pValidationLogTable VARCHAR2,
|
|
pTemplateSchema VARCHAR2,
|
|
pTemplateTable VARCHAR2,
|
|
pCsvFileUri VARCHAR2
|
|
) RETURN VARCHAR2;
|
|
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
-- PACKAGE VERSION MANAGEMENT FUNCTIONS
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
|
|
/**
|
|
* @name GET_VERSION
|
|
* @desc Returns the current version number of the ENV_MANAGER package.
|
|
* Uses semantic versioning format (MAJOR.MINOR.PATCH).
|
|
* @example SELECT ENV_MANAGER.GET_VERSION() FROM DUAL;
|
|
* @ex_rslt 3.0.0
|
|
**/
|
|
FUNCTION GET_VERSION RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name GET_BUILD_INFO
|
|
* @desc Returns comprehensive build information including version, build date, and author.
|
|
* Formatted for display in logs or monitoring systems.
|
|
* @example SELECT ENV_MANAGER.GET_BUILD_INFO() FROM DUAL;
|
|
* @ex_rslt Package: ENV_MANAGER
|
|
* Version: 3.0.0
|
|
* Build Date: 2025-10-22 16:00:00
|
|
* Author: Grzegorz Michalski
|
|
**/
|
|
FUNCTION GET_BUILD_INFO RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name GET_VERSION_HISTORY
|
|
* @desc Returns complete version history with all releases and changes.
|
|
* Shows evolution of package features over time.
|
|
* @example SELECT ENV_MANAGER.GET_VERSION_HISTORY() FROM DUAL;
|
|
* @ex_rslt ENV_MANAGER Version History:
|
|
* 3.0.0 (2025-10-22): Added package versioning system...
|
|
* 2.1.0 (2025-10-15): Added ANALYZE_VALIDATION_ERRORS function...
|
|
**/
|
|
FUNCTION GET_VERSION_HISTORY RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name GET_PACKAGE_VERSION_INFO
|
|
* @desc Universal function to get formatted version information for any package.
|
|
* This centralized function is used by all packages in the system.
|
|
* @param pPackageName - Name of the package
|
|
* @param pVersion - Version string (MAJOR.MINOR.PATCH format)
|
|
* @param pBuildDate - Build date timestamp
|
|
* @param pAuthor - Package author name
|
|
* @example SELECT ENV_MANAGER.GET_PACKAGE_VERSION_INFO('FILE_MANAGER', '2.1.0', '2025-10-22 15:00:00', 'Grzegorz Michalski') FROM DUAL;
|
|
* @ex_rslt Package: FILE_MANAGER
|
|
* Version: 2.1.0
|
|
* Build Date: 2025-10-22 15:00:00
|
|
* Author: Grzegorz Michalski
|
|
**/
|
|
FUNCTION GET_PACKAGE_VERSION_INFO(
|
|
pPackageName VARCHAR2,
|
|
pVersion VARCHAR2,
|
|
pBuildDate VARCHAR2,
|
|
pAuthor VARCHAR2
|
|
) RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name FORMAT_VERSION_HISTORY
|
|
* @desc Universal function to format version history for any package.
|
|
* Adds package name header and proper formatting.
|
|
* @param pPackageName - Name of the package
|
|
* @param pVersionHistory - Complete version history text
|
|
* @example SELECT ENV_MANAGER.FORMAT_VERSION_HISTORY('FILE_MANAGER', '2.1.0 (2025-10-22): Export procedures...') FROM DUAL;
|
|
* @ex_rslt FILE_MANAGER Version History:
|
|
* 2.1.0 (2025-10-22): Export procedures...
|
|
**/
|
|
FUNCTION FORMAT_VERSION_HISTORY(
|
|
pPackageName VARCHAR2,
|
|
pVersionHistory VARCHAR2
|
|
) RETURN VARCHAR2;
|
|
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
-- PACKAGE HASH + CHANGE DETECTION FUNCTIONS
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
|
|
/**
|
|
* @name CALCULATE_PACKAGE_HASH
|
|
* @desc Calculates SHA256 hash of package source code from ALL_SOURCE.
|
|
* Returns hash for both SPEC and BODY (if exists).
|
|
* Used for automatic change detection.
|
|
* @param pPackageOwner - Schema owner of the package
|
|
* @param pPackageName - Name of the package
|
|
* @param pPackageType - Type of package code ('PACKAGE' for SPEC, 'PACKAGE BODY' for BODY)
|
|
* @example SELECT ENV_MANAGER.CALCULATE_PACKAGE_HASH('CT_MRDS', 'FILE_MANAGER', 'PACKAGE') FROM DUAL;
|
|
* @ex_rslt A7B3C5D9E8F1234567890ABCDEF... (64-character SHA256 hash)
|
|
**/
|
|
FUNCTION CALCULATE_PACKAGE_HASH(
|
|
pPackageOwner VARCHAR2,
|
|
pPackageName VARCHAR2,
|
|
pPackageType VARCHAR2 -- 'PACKAGE' or 'PACKAGE BODY'
|
|
) RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name TRACK_PACKAGE_VERSION
|
|
* @desc Records package version and source code hash in A_PACKAGE_VERSION_TRACKING table.
|
|
* Automatically detects if source code changed without version update.
|
|
* Should be called after every package deployment.
|
|
* @param pPackageOwner - Schema owner of the package
|
|
* @param pPackageName - Name of the package
|
|
* @param pPackageVersion - Current version from PACKAGE_VERSION constant
|
|
* @param pPackageBuildDate - Build date from PACKAGE_BUILD_DATE constant
|
|
* @param pPackageAuthor - Author from PACKAGE_AUTHOR constant
|
|
* @example EXEC ENV_MANAGER.TRACK_PACKAGE_VERSION('CT_MRDS', 'FILE_MANAGER', '3.2.0', '2025-10-22 16:30:00', 'Grzegorz Michalski');
|
|
* @ex_rslt Record inserted into A_PACKAGE_VERSION_TRACKING with change detection status
|
|
**/
|
|
PROCEDURE TRACK_PACKAGE_VERSION(
|
|
pPackageOwner VARCHAR2,
|
|
pPackageName VARCHAR2,
|
|
pPackageVersion VARCHAR2,
|
|
pPackageBuildDate VARCHAR2,
|
|
pPackageAuthor VARCHAR2
|
|
);
|
|
|
|
/**
|
|
* @name CHECK_PACKAGE_CHANGES
|
|
* @desc Checks if package source code has changed since last tracking.
|
|
* Compares current hash with last recorded hash in A_PACKAGE_VERSION_TRACKING.
|
|
* Returns detailed change detection report.
|
|
* @param pPackageOwner - Schema owner of the package
|
|
* @param pPackageName - Name of the package
|
|
* @example SELECT ENV_MANAGER.CHECK_PACKAGE_CHANGES('CT_MRDS', 'FILE_MANAGER') FROM DUAL;
|
|
* @ex_rslt WARNING: Package changed without version update!
|
|
* Last Version: 3.2.0
|
|
* Current Hash (SPEC): A7B3C5D9...
|
|
* Last Hash (SPEC): B8C4D6E0...
|
|
* RECOMMENDATION: Update PACKAGE_VERSION and PACKAGE_BUILD_DATE
|
|
**/
|
|
FUNCTION CHECK_PACKAGE_CHANGES(
|
|
pPackageOwner VARCHAR2,
|
|
pPackageName VARCHAR2
|
|
) RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name GET_PACKAGE_HASH_INFO
|
|
* @desc Returns formatted information about package hash and tracking history.
|
|
* Includes current hash, last tracked hash, and change detection status.
|
|
* @param pPackageOwner - Schema owner of the package
|
|
* @param pPackageName - Name of the package
|
|
* @example SELECT ENV_MANAGER.GET_PACKAGE_HASH_INFO('CT_MRDS', 'FILE_MANAGER') FROM DUAL;
|
|
* @ex_rslt Package: CT_MRDS.FILE_MANAGER
|
|
* Current Version: 3.2.0
|
|
* Current Hash (SPEC): A7B3C5D9...
|
|
* Last Tracked: 2025-10-22 16:30:00
|
|
* Status: OK - No changes detected
|
|
**/
|
|
FUNCTION GET_PACKAGE_HASH_INFO(
|
|
pPackageOwner VARCHAR2,
|
|
pPackageName VARCHAR2
|
|
) RETURN VARCHAR2;
|
|
|
|
END ENV_MANAGER;
|