617 lines
26 KiB
Plaintext
617 lines
26 KiB
Plaintext
create or replace PACKAGE CT_MRDS.FILE_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 FILE_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.2.0';
|
|
PACKAGE_BUILD_DATE CONSTANT VARCHAR2(20) := '2025-10-22 16:30:00';
|
|
PACKAGE_AUTHOR CONSTANT VARCHAR2(100) := 'Grzegorz Michalski';
|
|
|
|
-- Version History (Latest changes first)
|
|
VERSION_HISTORY CONSTANT VARCHAR2(4000) :=
|
|
'3.2.0 (2025-10-22): Added package versioning system using centralized ENV_MANAGER functions' || CHR(13)||CHR(10) ||
|
|
'3.1.0 (2025-10-20): Enhanced PROCESS_SOURCE_FILE with 6-step validation workflow' || CHR(13)||CHR(10) ||
|
|
'3.0.0 (2025-10-15): Separated export procedures into dedicated DATA_EXPORTER package' || CHR(13)||CHR(10) ||
|
|
'2.5.0 (2025-10-10): Added DELETE_SOURCE_CASCADE for safe configuration removal' || CHR(13)||CHR(10) ||
|
|
'2.0.0 (2025-09-25): Added official path patterns support (INBOX 3-level, ODS 2-level, ARCHIVE 2-level)' || CHR(13)||CHR(10) ||
|
|
'1.0.0 (2025-09-01): Initial release with file processing and validation capabilities';
|
|
|
|
TYPE tSourceFileReceived IS RECORD
|
|
(
|
|
A_SOURCE_FILE_RECEIVED_KEY CT_MRDS.A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY%TYPE,
|
|
A_SOURCE_FILE_CONFIG_KEY CT_MRDS.A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_CONFIG_KEY%TYPE,
|
|
SOURCE_FILE_PREFIX_INBOX VARCHAR2(430),
|
|
SOURCE_FILE_PREFIX_ODS VARCHAR2(430),
|
|
SOURCE_FILE_PREFIX_QUARANTINE VARCHAR2(430),
|
|
SOURCE_FILE_PREFIX_ARCHIVE VARCHAR2(430),
|
|
SOURCE_FILE_NAME CT_MRDS.A_SOURCE_FILE_RECEIVED.SOURCE_FILE_NAME%TYPE,
|
|
RECEPTION_DATE CT_MRDS.A_SOURCE_FILE_RECEIVED.RECEPTION_DATE%TYPE,
|
|
PROCESSING_STATUS CT_MRDS.A_SOURCE_FILE_RECEIVED.PROCESSING_STATUS%TYPE,
|
|
EXTERNAL_TABLE_NAME CT_MRDS.A_SOURCE_FILE_RECEIVED.EXTERNAL_TABLE_NAME%TYPE
|
|
);
|
|
|
|
cgBL CONSTANT VARCHAR2(2) := CHR(13)||CHR(10);
|
|
vgSourceFileConfigKey PLS_INTEGER;
|
|
vgMsgTmp VARCHAR2(32000);
|
|
|
|
|
|
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
|
|
/**
|
|
* @name GET_SOURCE_FILE_CONFIG
|
|
* @desc Get source file type by matching the source file name against source file type naming patterns
|
|
* or by specifying the id of a received source file.
|
|
* @example ...
|
|
* @ex_rslt "CT_MRDS.A_SOURCE_FILE_CONFIG%ROWTYPE"
|
|
**/
|
|
FUNCTION GET_SOURCE_FILE_CONFIG(pFileUri IN VARCHAR2 DEFAULT NULL
|
|
, pSourceFileReceivedKey IN NUMBER DEFAULT NULL
|
|
, pSourceFileConfigKey IN NUMBER DEFAULT NULL)
|
|
RETURN CT_MRDS.A_SOURCE_FILE_CONFIG%ROWTYPE;
|
|
|
|
|
|
|
|
/**
|
|
* @name REGISTER_SOURCE_FILE_RECEIVED
|
|
* @desc Register a newly received source file in A_SOURCE_FILE_RECEIVED table.
|
|
* This overload automatically determines source file type from the file name.
|
|
* It returns the value of A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY column for newly added record.
|
|
* @example vSourceFileReceivedKey := FILE_MANAGER.REGISTER_SOURCE_FILE_RECEIVED(pSourceFileReceivedName => 'INBOX/C2D/UC_DISSEM/UC_NMA_DISSEM/UC_NMA_DISSEM-277740.csv');
|
|
* @ex_rslt 3245
|
|
**/
|
|
FUNCTION REGISTER_SOURCE_FILE_RECEIVED (
|
|
pSourceFileReceivedName IN VARCHAR2
|
|
)
|
|
RETURN PLS_INTEGER;
|
|
|
|
|
|
|
|
/**
|
|
* @name REGISTER_SOURCE_FILE_RECEIVED
|
|
* @desc Register a new new source file in A_SOURCE_FILE_RECEIVED table based on pSourceFileReceivedName and pSourceFileConfig.
|
|
* Then it returns the value of A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY column for newly added record.
|
|
* @example vSourceFileReceivedKey := FILE_MANAGER.REGISTER_SOURCE_FILE_RECEIVED(
|
|
* pSourceFileReceivedName => 'INBOX/C2D/UC_DISSEM/UC_NMA_DISSEM/UC_NMA_DISSEM-277740.csv'
|
|
* ,pSourceFileConfig => ...A_SOURCE_FILE_CONFIG%ROWTYPE... );
|
|
* @ex_rslt 3245
|
|
**/
|
|
FUNCTION REGISTER_SOURCE_FILE_RECEIVED (
|
|
pSourceFileReceivedName IN VARCHAR2,
|
|
pSourceFileConfig IN CT_MRDS.A_SOURCE_FILE_CONFIG%ROWTYPE
|
|
)
|
|
RETURN PLS_INTEGER;
|
|
|
|
|
|
|
|
/**
|
|
* @name SET_SOURCE_FILE_RECEIVED_STATUS
|
|
* @desc Set status of file in A_SOURCE_FILE_RECEIVED table - PROCESSING_STATUS column
|
|
* based on A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY
|
|
* and provided value of pStatus parameter
|
|
* @example exec FILE_MANAGER.SET_SOURCE_FILE_RECEIVED_STATUS(pSourceFileReceivedKey => 377, pStatus => 'READY_FOR_INGESTION');
|
|
**/
|
|
PROCEDURE SET_SOURCE_FILE_RECEIVED_STATUS(
|
|
pSourceFileReceivedKey IN PLS_INTEGER,
|
|
pStatus IN VARCHAR2
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_EXTERNAL_TABLE_COLUMNS
|
|
* @desc Function used to get string with all table columns definitions based on pTargetTableTemplate "TEMPLATE TABLE" name.
|
|
* It used for creating "EXTERNAL TABLE" using CREATE_EXTERNAL_TABLE procedure.
|
|
* @example select FILE_MANAGER.GET_EXTERNAL_TABLE_COLUMNS(pTargetTableTemplate => 'CT_ET_TEMPLATES.LM_STANDING_FACILITIES_HEADER') from dual;
|
|
* @ex_rslt "A_KEY" NUMBER(38,0) NOT NULL ENABLE,
|
|
* "A_WORKFLOW_HISTORY_KEY" NUMBER(38,0) NOT NULL ENABLE,
|
|
* "REV_NUMBER" NUMBER(28,0),
|
|
* "REF_DATE" DATE,
|
|
* "FREE_TEXT" VARCHAR2(1000 CHAR),
|
|
* "MLF_BS_TOTAL" NUMBER(28,10),
|
|
* "DF_BS_TOTAL" NUMBER(28,10),
|
|
* "MLF_SF_TOTAL" NUMBER(28,10),
|
|
* "DF_SF_TOTAL" NUMBER(28,10)
|
|
**/
|
|
FUNCTION GET_EXTERNAL_TABLE_COLUMNS (
|
|
pTargetTableTemplate IN VARCHAR2
|
|
)
|
|
RETURN CLOB;
|
|
|
|
|
|
|
|
/**
|
|
* @name CREATE_EXTERNAL_TABLE
|
|
* @desc A wrapper procedure for DBMS_CLOUD.CREATE_EXTERNAL_TABLE which creates External Table
|
|
* @example
|
|
* begin
|
|
* FILE_MANAGER.CREATE_EXTERNAL_TABLE(
|
|
* pTableName => 'STANDING_FACILITIES_HEADER',
|
|
* pTemplateTableName => 'CT_ET_TEMPLATES.LM_STANDING_FACILITIES_HEADER',
|
|
* pPrefix => 'ODS/LM/STANDING_FACILITIES_HEADER/',
|
|
* pBucketUri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frcnomajoc7v/b/mrds_data_tst/o/'
|
|
* pFileName => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frcnomajoc7v/b/mrds_data_tst/o/ODS/LM/STANDING_FACILITIES_HEADER/*.csv',
|
|
* pDelimiter => ',',
|
|
|
|
* );
|
|
* end;
|
|
**/
|
|
PROCEDURE CREATE_EXTERNAL_TABLE (
|
|
pTableName IN VARCHAR2,
|
|
pTemplateTableName IN VARCHAR2,
|
|
pPrefix IN VARCHAR2,
|
|
pBucketUri IN VARCHAR2 DEFAULT ENV_MANAGER.gvInboxBucketUri,
|
|
pFileName IN VARCHAR2 DEFAULT NULL,
|
|
pDelimiter IN VARCHAR2 DEFAULT ','
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name CREATE_EXTERNAL_TABLE
|
|
* @desc Creates External Table for single file provided by
|
|
* pSourceFileReceivedKey parameter (A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY)
|
|
* @example exec FILE_MANAGER.CREATE_EXTERNAL_TABLE(pSourceFileReceivedKey => 377);;
|
|
**/
|
|
PROCEDURE CREATE_EXTERNAL_TABLE (
|
|
pSourceFileReceivedKey IN NUMBER
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name VALIDATE_SOURCE_FILE_RECEIVED
|
|
* @desc A wrapper procedure for DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE
|
|
* It validate External table build upon single file
|
|
* provided by pSourceFileReceivedKey parameter (A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY)
|
|
* @example exec FILE_MANAGER.VALIDATE_SOURCE_FILE_RECEIVED(pSourceFileReceivedKey => 377);
|
|
**/
|
|
PROCEDURE VALIDATE_SOURCE_FILE_RECEIVED
|
|
(
|
|
pSourceFileReceivedKey IN NUMBER
|
|
);
|
|
|
|
|
|
/**
|
|
* @name VALIDATE_EXTERNAL_TABLE
|
|
* @desc A wrapper function for DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE.
|
|
* It validates External Table provided by parameter pTableName.
|
|
* It returns: PASSED or FAILED.
|
|
* @example
|
|
* declare
|
|
* vStatus VARCHAR2(100);
|
|
* begin
|
|
* vStatus := FILE_MANAGER.VALIDATE_EXTERNAL_TABLE(pTableName => 'STANDING_FACILITIES_HEADER');
|
|
* DBMS_OUTPUT.PUT_LINE('vStatus = '||vStatus);
|
|
* end;
|
|
*
|
|
* @ex_rslt FAILED
|
|
**/
|
|
FUNCTION VALIDATE_EXTERNAL_TABLE(pTableName IN VARCHAR2)
|
|
RETURN VARCHAR2;
|
|
|
|
|
|
/**
|
|
* @name S_VALIDATE_EXTERNAL_TABLE
|
|
* @desc A function which checks if SELECT query reterns any rows.
|
|
* It trys to selects External Table provided by parameter pTableName.
|
|
* It returns: PASSED or FAILED.
|
|
* @example
|
|
* declare
|
|
* vStatus VARCHAR2(100);
|
|
* begin
|
|
* vStatus := FILE_MANAGER.S_VALIDATE_EXTERNAL_TABLE(pTableName => 'STANDING_FACILITIES_HEADER');
|
|
* DBMS_OUTPUT.PUT_LINE('vStatus = '||vStatus);
|
|
* end;
|
|
*
|
|
* @ex_rslt PASSED
|
|
**/
|
|
FUNCTION S_VALIDATE_EXTERNAL_TABLE(pTableName IN VARCHAR2)
|
|
RETURN VARCHAR2;
|
|
|
|
|
|
|
|
/**
|
|
* @name DROP_EXTERNAL_TABLE
|
|
* @desc It drops External Table for single file provided by
|
|
* pSourceFileReceivedKey parameter (A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY)
|
|
* @example exec FILE_MANAGER.DROP_EXTERNAL_TABLE(pSourceFileReceivedKey => 377);
|
|
**/
|
|
PROCEDURE DROP_EXTERNAL_TABLE (
|
|
pSourceFileReceivedKey IN NUMBER
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name COPY_FILE
|
|
* @desc It copies file provided by
|
|
* pSourceFileReceivedKey parameter (A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY)
|
|
* into destination provided by pDestination parameter.
|
|
* pDestination parameter allowed values are: 'ODS'
|
|
* @example exec FILE_MANAGER.COPY_FILE(pSourceFileReceivedKey => 377, pDestination => 'ODS');
|
|
**/
|
|
PROCEDURE COPY_FILE(
|
|
pSourceFileReceivedKey IN NUMBER,
|
|
pDestination IN VARCHAR2
|
|
);
|
|
|
|
|
|
|
|
|
|
/**
|
|
* @name MOVE_FILE
|
|
* @desc It moves file provided by
|
|
* pSourceFileReceivedKey parameter (A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY)
|
|
* into destination provided by pDestination parameter.
|
|
* pDestination parameter allowed values are: 'ODS', 'QUARANTINE'
|
|
* @example exec FILE_MANAGER.MOVE_FILE(pSourceFileReceivedKey => 377, pDestination => 'ODS');
|
|
**/
|
|
PROCEDURE MOVE_FILE(
|
|
pSourceFileReceivedKey IN NUMBER,
|
|
pDestination IN VARCHAR2
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name DELETE_FOLDER_CONTENTS
|
|
* @desc It deletes all files from specified folder in the cloud storage.
|
|
* The procedure lists all objects in the specified folder prefix and deletes them one by one.
|
|
* pBucketArea parameter specifies which bucket to use: 'INBOX', 'DATA', 'ARCHIVE'
|
|
* pFolderPrefix parameter specifies the folder path within the bucket (e.g., 'C2D/UC_DISSEM/UC_NMA_DISSEM/')
|
|
* @example exec FILE_MANAGER.DELETE_FOLDER_CONTENTS(pBucketArea => 'INBOX', pFolderPrefix => 'C2D/UC_DISSEM/UC_NMA_DISSEM/');
|
|
**/
|
|
PROCEDURE DELETE_FOLDER_CONTENTS(
|
|
pBucketArea IN VARCHAR2,
|
|
pFolderPrefix IN VARCHAR2
|
|
);
|
|
|
|
|
|
|
|
|
|
/**
|
|
* @name PROCESS_SOURCE_FILE
|
|
* @desc It process file provided by pSourceFileReceivedName parameter.
|
|
* Ubmrella procedure that calls:
|
|
* - REGISTER_SOURCE_FILE_RECEIVED;
|
|
* - CREATE_EXTERNAL_TABLE;
|
|
* - VALIDATE_SOURCE_FILE_RECEIVED;
|
|
* - DROP_EXTERNAL_TABLE;
|
|
* - MOVE_FILE;
|
|
* @example exec FILE_MANAGER.PROCESS_SOURCE_FILE(pSourceFileReceivedName => 'INBOX/C2D/UC_DISSEM/UC_NMA_DISSEM/UC_NMA_DISSEM-277740.csv');
|
|
**/
|
|
PROCEDURE PROCESS_SOURCE_FILE(pSourceFileReceivedName IN VARCHAR2)
|
|
;
|
|
|
|
|
|
|
|
/**
|
|
* @name PROCESS_SOURCE_FILE
|
|
* @desc It process file provided by pSourceFileReceivedName parameter and return processing result value.
|
|
* It returns (success/failure) => 0 / -(value).
|
|
* Ubmrella function that calls PROCESS_SOURCE_FILE procedure.
|
|
* @example
|
|
* declare
|
|
* vResult PLS_INTEGER;
|
|
* begin
|
|
* vResult := CT_MRDS.FILE_MANAGER.PROCESS_SOURCE_FILE(PSOURCEFILERECEIVEDNAME => 'INBOX/C2D/UC_DISSEM/UC_NMA_DISSEM/UC_NMA_DISSEM-277740.csv');
|
|
* DBMS_OUTPUT.PUT_LINE('vResult = ' || vResult);
|
|
* end;
|
|
* @ex_rslt 0
|
|
* -20021
|
|
**/
|
|
FUNCTION PROCESS_SOURCE_FILE(pSourceFileReceivedName IN VARCHAR2)
|
|
RETURN PLS_INTEGER;
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_DATE_FORMAT
|
|
* @desc Returns date format for specified template table name and column name.
|
|
* Date is taken from configuration A_COLUMN_DATE_FORMAT table.
|
|
* @example select FILE_MANAGER.GET_DATE_FORMAT(
|
|
* pTemplateTableName => 'STANDING_FACILITIES_HEADER',
|
|
* pColumnName => 'SNAPSHOT_DATE')
|
|
* from dual;
|
|
* @ex_rslt DD/MM/YYYY HH24:MI:SS
|
|
**/
|
|
FUNCTION GET_DATE_FORMAT(
|
|
pTemplateTableName IN VARCHAR2,
|
|
pColumnName IN VARCHAR2
|
|
) RETURN VARCHAR2;
|
|
|
|
|
|
|
|
/**
|
|
* @name GENERATE_EXTERNAL_TABLE_PARAMS
|
|
* @desc It builds two strings: pColumnList and pFieldList for specified Template Table name, by parameter: pTemplateTableName.
|
|
* @example
|
|
* declare
|
|
* vColumnList CLOB;
|
|
* vFieldList CLOB;
|
|
* begin
|
|
* FILE_MANAGER.GENERATE_EXTERNAL_TABLE_PARAMS (
|
|
* pTemplateTableName => 'CT_ET_TEMPLATES.LM_STANDING_FACILITIES_HEADER'
|
|
* ,pColumnList => vColumnList
|
|
* ,pFieldList => vFieldList
|
|
* );
|
|
* DBMS_OUTPUT.PUT_LINE('vColumnList = '||vColumnList);
|
|
* DBMS_OUTPUT.PUT_LINE('vFieldList = '||vFieldList);
|
|
* end;
|
|
* /
|
|
**/
|
|
PROCEDURE GENERATE_EXTERNAL_TABLE_PARAMS (
|
|
pTemplateTableName IN VARCHAR2,
|
|
pColumnList OUT CLOB,
|
|
pFieldList OUT CLOB
|
|
);
|
|
|
|
|
|
|
|
|
|
|
|
/**
|
|
* @name ADD_SOURCE
|
|
* @desc Insert a new record to A_SOURCE table.
|
|
* pSourceKey is a PRIMARY KEY value.
|
|
**/
|
|
PROCEDURE ADD_SOURCE (
|
|
pSourceKey IN CT_MRDS.A_SOURCE.A_SOURCE_KEY%TYPE,
|
|
pSourceName IN CT_MRDS.A_SOURCE.SOURCE_NAME%TYPE
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name DELETE_SOURCE_CASCADE
|
|
* @desc Safely deletes a SOURCE specified by pSourceKey parameter from A_SOURCE table and all dependent tables:
|
|
* - A_SOURCE_FILE_CONFIG
|
|
* - A_SOURCE_FILE_RECEIVED
|
|
* - A_COLUMN_DATE_FORMAT (only if template table is not shared with other source systems)
|
|
* The procedure checks if template tables are shared before deleting date format configurations.
|
|
* If a template table is used by multiple source systems, date formats are preserved.
|
|
* @example CALL CT_MRDS.FILE_MANAGER.DELETE_SOURCE_CASCADE(pSourceKey => 'TEST_SYS');
|
|
**/
|
|
PROCEDURE DELETE_SOURCE_CASCADE (
|
|
pSourceKey IN CT_MRDS.A_SOURCE.A_SOURCE_KEY%TYPE
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_CONTAINER_SOURCE_FILE_CONFIG_KEY
|
|
* @desc For specified parameter pSourceFileId (A_SOURCE_FILE_CONFIG.SOURCE_FILE_ID)
|
|
* it returns A_SOURCE_FILE_CONFIG.A_SOURCE_FILE_CONFIG_KEY for related CONTAINER record.
|
|
* @example select FILE_MANAGER.GET_CONTAINER_SOURCE_FILE_CONFIG_KEY(
|
|
* pSourceFileId => 'UC_DISSEM')
|
|
* from dual;
|
|
* @ex_rslt 126
|
|
**/
|
|
FUNCTION GET_CONTAINER_SOURCE_FILE_CONFIG_KEY (
|
|
pSourceFileId IN CT_MRDS.A_SOURCE_FILE_CONFIG.SOURCE_FILE_ID%TYPE
|
|
) RETURN PLS_INTEGER;
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_SOURCE_FILE_CONFIG_KEY
|
|
* @desc For specified input parameters,
|
|
* it returns A_SOURCE_FILE_CONFIG.A_SOURCE_FILE_CONFIG_KEY.
|
|
* @example select FILE_MANAGER.GET_SOURCE_FILE_CONFIG_KEY (
|
|
* pSourceFileType => 'INPUT'
|
|
* ,pSourceFileId => 'UC_DISSEM'
|
|
* ,pTableId => 'UC_NMA_DISSEM')
|
|
* from dual;
|
|
* @ex_rslt 126
|
|
**/
|
|
FUNCTION GET_SOURCE_FILE_CONFIG_KEY (
|
|
pSourceFileType IN CT_MRDS.A_SOURCE_FILE_CONFIG.SOURCE_FILE_TYPE%TYPE DEFAULT 'INPUT'
|
|
,pSourceFileId IN CT_MRDS.A_SOURCE_FILE_CONFIG.SOURCE_FILE_ID%TYPE
|
|
,pTableId IN CT_MRDS.A_SOURCE_FILE_CONFIG.TABLE_ID%TYPE
|
|
) RETURN PLS_INTEGER;
|
|
|
|
|
|
|
|
/**
|
|
* @name ADD_SOURCE_FILE_CONFIG
|
|
* @desc Insert a new record to A_SOURCE_FILE_CONFIG table.
|
|
**/
|
|
PROCEDURE ADD_SOURCE_FILE_CONFIG (
|
|
pSourceKey IN CT_MRDS.A_SOURCE_FILE_CONFIG.A_SOURCE_KEY%TYPE
|
|
,pSourceFileType IN CT_MRDS.A_SOURCE_FILE_CONFIG.SOURCE_FILE_TYPE%TYPE
|
|
,pSourceFileId IN CT_MRDS.A_SOURCE_FILE_CONFIG.SOURCE_FILE_ID%TYPE
|
|
,pSourceFileDesc IN CT_MRDS.A_SOURCE_FILE_CONFIG.SOURCE_FILE_DESC%TYPE
|
|
,pSourceFileNamePattern IN CT_MRDS.A_SOURCE_FILE_CONFIG.SOURCE_FILE_NAME_PATTERN%TYPE
|
|
,pTableId IN CT_MRDS.A_SOURCE_FILE_CONFIG.TABLE_ID%TYPE DEFAULT NULL
|
|
,pTemplateTableName IN CT_MRDS.A_SOURCE_FILE_CONFIG.TEMPLATE_TABLE_NAME%TYPE DEFAULT NULL
|
|
,pContainerFileKey IN CT_MRDS.A_SOURCE_FILE_CONFIG.CONTAINER_FILE_KEY%TYPE DEFAULT NULL
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name ADD_COLUMN_DATE_FORMAT
|
|
* @desc Insert a new record to A_COLUMN_DATE_FORMAT table.
|
|
**/
|
|
PROCEDURE ADD_COLUMN_DATE_FORMAT (
|
|
pTemplateTableName IN CT_MRDS.A_COLUMN_DATE_FORMAT.TEMPLATE_TABLE_NAME%TYPE
|
|
,pColumnName IN CT_MRDS.A_COLUMN_DATE_FORMAT.COLUMN_NAME%TYPE
|
|
,pDateFormat IN CT_MRDS.A_COLUMN_DATE_FORMAT.DATE_FORMAT%TYPE
|
|
);
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_BUCKET_URI
|
|
* @desc Function used to get string with bucket http url.
|
|
* Possible input values for pBucketArea are: 'INBOX', 'ODS', 'DATA', 'ARCHIVE'
|
|
* @example select FILE_MANAGER.GET_BUCKET_URI(pBucketArea => 'ODS') from dual;
|
|
* @ex_rslt https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frcnomajoc7v/b/mrds_data_tst/o/
|
|
**/
|
|
FUNCTION GET_BUCKET_URI(pBucketArea VARCHAR2)
|
|
RETURN VARCHAR2;
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_DET_SOURCE_FILE_CONFIG_INFO
|
|
* @desc Function returns details about A_SOURCE_FILE_CONFIG record
|
|
* for specified pSourceFileConfigKey (A_SOURCE_FILE_CONFIG.A_SOURCE_FILE_CONFIG_KEY).
|
|
* If pIncludeContainerInfo is <> 0 it returns additional info about related Container config record (A_SOURCE_FILE_CONFIG)
|
|
* If pIncludeColumnFormatInfo is <> 0 it returns additional info about related ColumnFormat config record (A_COLUMN_DATE_FORMAT)
|
|
* @example select FILE_MANAGER.GET_DET_SOURCE_FILE_CONFIG_INFO (
|
|
* pSourceFileConfigKey => 128
|
|
* ,pIncludeContainerInfo => 1
|
|
* ,pIncludeColumnFormatInfo => 1
|
|
* ) from dual;
|
|
* @ex_rslt
|
|
* Details about File Configuration:
|
|
* --------------------------------
|
|
* A_SOURCE_FILE_CONFIG_KEY = 128
|
|
* A_SOURCE_KEY = C2D
|
|
* ...
|
|
* --------------------------------
|
|
*
|
|
* Details about related Container Config:
|
|
* --------------------------------
|
|
* A_SOURCE_FILE_CONFIG_KEY = 126
|
|
* A_SOURCE_KEY = C2D
|
|
* ...
|
|
* --------------------------------
|
|
*
|
|
* Column Date Format config entries:
|
|
* --------------------------------
|
|
* TEMPLATE_TABLE_NAME = CT_ET_TEMPLATES.C2D_UC_MA_DISSEM
|
|
* ...
|
|
* --------------------------------
|
|
**/
|
|
FUNCTION GET_DET_SOURCE_FILE_CONFIG_INFO (
|
|
pSourceFileConfigKey IN CT_MRDS.A_SOURCE_FILE_CONFIG.A_SOURCE_FILE_CONFIG_KEY%TYPE
|
|
,pIncludeContainerInfo IN PLS_INTEGER DEFAULT 1
|
|
,pIncludeColumnFormatInfo IN PLS_INTEGER DEFAULT 1
|
|
) RETURN VARCHAR2;
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_DET_SOURCE_FILE_RECEIVED_INFO
|
|
* @desc Function returns details about A_SOURCE_FILE_RECEIVED record
|
|
* for specified pSourceFileReceivedKey (A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY).
|
|
* If pIncludeConfigInfo is <> 0 it returns additional info about related Container config record (A_SOURCE_FILE_CONFIG)
|
|
* If pIncludeContainerInfo is <> 0 it returns additional info about related Container config record (A_SOURCE_FILE_CONFIG)
|
|
* If pIncludeColumnFormatInfo is <> 0 it returns additional info about related ColumnFormat config record (A_COLUMN_DATE_FORMAT)
|
|
* @example select FILE_MANAGER.GET_DET_SOURCE_FILE_RECEIVED_INFO (
|
|
* pSourceFileReceivedKey => 377
|
|
* ,pIncludeConfigInfo => 1
|
|
* ,pIncludeContainerInfo => 1
|
|
* ,pIncludeColumnFormatInfo => 1
|
|
* ) from dual;
|
|
*
|
|
**/
|
|
FUNCTION GET_DET_SOURCE_FILE_RECEIVED_INFO (
|
|
pSourceFileReceivedKey IN CT_MRDS.A_SOURCE_FILE_RECEIVED.A_SOURCE_FILE_RECEIVED_KEY%TYPE
|
|
,pIncludeConfigInfo IN PLS_INTEGER DEFAULT 1
|
|
,pIncludeContainerInfo IN PLS_INTEGER DEFAULT 1
|
|
,pIncludeColumnFormatInfo IN PLS_INTEGER DEFAULT 1
|
|
) RETURN VARCHAR2;
|
|
|
|
|
|
|
|
/**
|
|
* @name GET_DET_USER_LOAD_OPERATIONS
|
|
* @desc Function returns details from USER_LOAD_OPERATIONS table
|
|
* for specified pOperationId.
|
|
* @example select FILE_MANAGER.GET_DET_USER_LOAD_OPERATIONS (pOperationId => 3608) from dual;
|
|
* @ex_rslt
|
|
* Details about USER_LOAD_OPERATIONS where ID = 3608
|
|
* --------------------------------
|
|
* ID = 3608
|
|
* TYPE = VALIDATE
|
|
* SID = 31260
|
|
* SERIAL# = 52915
|
|
* START_TIME = 2025-05-20 10.08.24.436983 EUROPE/BELGRADE
|
|
* UPDATE_TIME = 2025-05-20 10.08.24.458643 EUROPE/BELGRADE
|
|
* STATUS = FAILED
|
|
* OWNER_NAME = CT_MRDS
|
|
* TABLE_NAME = STANDING_FACILITIES_HEADER
|
|
* PARTITION_NAME =
|
|
* SUBPARTITION_NAME =
|
|
* FILE_URI_LIST =
|
|
* ROWS_LOADED =
|
|
* LOGFILE_TABLE = VALIDATE$3608_LOG
|
|
* BADFILE_TABLE = VALIDATE$3608_BAD
|
|
* STATUS_TABLE =
|
|
* TEMPEXT_TABLE =
|
|
* CREDENTIAL_NAME =
|
|
* EXPIRATION_TIME = 2025-05-22 10.08.24.436983000 EUROPE/BELGRADE
|
|
* --------------------------------
|
|
**/
|
|
FUNCTION GET_DET_USER_LOAD_OPERATIONS (
|
|
pOperationId PLS_INTEGER
|
|
) RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name ANALYZE_VALIDATION_ERRORS
|
|
* @desc Wrapper function that analyzes validation errors for a source file using its received key.
|
|
* Automatically derives template schema, table name, CSV URI and validation log table
|
|
* from file metadata and calls ENV_MANAGER.ANALYZE_VALIDATION_ERRORS.
|
|
* @example SELECT FILE_MANAGER.ANALYZE_VALIDATION_ERRORS(63) FROM DUAL;
|
|
* @ex_rslt Detailed validation analysis report with column mismatches and solutions
|
|
**/
|
|
FUNCTION ANALYZE_VALIDATION_ERRORS(
|
|
pSourceFileReceivedKey IN NUMBER
|
|
) RETURN VARCHAR2;
|
|
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
-- PACKAGE VERSION MANAGEMENT FUNCTIONS
|
|
---------------------------------------------------------------------------------------------------------------------------
|
|
|
|
/**
|
|
* @name GET_VERSION
|
|
* @desc Returns the current version number of the FILE_MANAGER package.
|
|
* Uses semantic versioning format (MAJOR.MINOR.PATCH).
|
|
* @example SELECT FILE_MANAGER.GET_VERSION() FROM DUAL;
|
|
* @ex_rslt 3.2.0
|
|
**/
|
|
FUNCTION GET_VERSION RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name GET_BUILD_INFO
|
|
* @desc Returns comprehensive build information including version, build date, and author.
|
|
* Uses centralized ENV_MANAGER.GET_PACKAGE_VERSION_INFO function.
|
|
* @example SELECT FILE_MANAGER.GET_BUILD_INFO() FROM DUAL;
|
|
* @ex_rslt Package: FILE_MANAGER
|
|
* Version: 3.2.0
|
|
* Build Date: 2025-10-22 16:30:00
|
|
* Author: Grzegorz Michalski
|
|
**/
|
|
FUNCTION GET_BUILD_INFO RETURN VARCHAR2;
|
|
|
|
/**
|
|
* @name GET_VERSION_HISTORY
|
|
* @desc Returns complete version history with all releases and changes.
|
|
* Uses centralized ENV_MANAGER.FORMAT_VERSION_HISTORY function.
|
|
* @example SELECT FILE_MANAGER.GET_VERSION_HISTORY() FROM DUAL;
|
|
* @ex_rslt FILE_MANAGER Version History:
|
|
* 3.2.0 (2025-10-22): Added package versioning system...
|
|
**/
|
|
FUNCTION GET_VERSION_HISTORY RETURN VARCHAR2;
|
|
|
|
END;
|