150 lines
5.2 KiB
SQL
150 lines
5.2 KiB
SQL
-- ====================================================================
|
|
-- TRACK_ALL_PACKAGES.SQL
|
|
-- ====================================================================
|
|
-- Purpose: Track all packages with versioning system
|
|
-- Records current version and source code hash for change detection
|
|
-- Should be run after every package deployment
|
|
-- ====================================================================
|
|
|
|
SET SERVEROUTPUT ON SIZE UNLIMITED
|
|
SET LINESIZE 200
|
|
|
|
PROMPT
|
|
PROMPT ====================================================================
|
|
PROMPT TRACKING ALL PACKAGES - Recording Versions and Hashes
|
|
PROMPT ====================================================================
|
|
PROMPT
|
|
|
|
-- Track ENV_MANAGER
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('Tracking ENV_MANAGER...');
|
|
CT_MRDS.ENV_MANAGER.TRACK_PACKAGE_VERSION(
|
|
pPackageOwner => 'CT_MRDS',
|
|
pPackageName => 'ENV_MANAGER',
|
|
pPackageVersion => CT_MRDS.ENV_MANAGER.PACKAGE_VERSION,
|
|
pPackageBuildDate => CT_MRDS.ENV_MANAGER.PACKAGE_BUILD_DATE,
|
|
pPackageAuthor => CT_MRDS.ENV_MANAGER.PACKAGE_AUTHOR
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('ENV_MANAGER tracked successfully.');
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
DBMS_OUTPUT.PUT_LINE('ERROR tracking ENV_MANAGER: ' || SQLERRM);
|
|
END;
|
|
/
|
|
|
|
-- Track FILE_MANAGER
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('Tracking FILE_MANAGER...');
|
|
CT_MRDS.ENV_MANAGER.TRACK_PACKAGE_VERSION(
|
|
pPackageOwner => 'CT_MRDS',
|
|
pPackageName => 'FILE_MANAGER',
|
|
pPackageVersion => CT_MRDS.FILE_MANAGER.PACKAGE_VERSION,
|
|
pPackageBuildDate => CT_MRDS.FILE_MANAGER.PACKAGE_BUILD_DATE,
|
|
pPackageAuthor => CT_MRDS.FILE_MANAGER.PACKAGE_AUTHOR
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('FILE_MANAGER tracked successfully.');
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
DBMS_OUTPUT.PUT_LINE('ERROR tracking FILE_MANAGER: ' || SQLERRM);
|
|
END;
|
|
/
|
|
|
|
-- Track DATA_EXPORTER
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('Tracking DATA_EXPORTER...');
|
|
CT_MRDS.ENV_MANAGER.TRACK_PACKAGE_VERSION(
|
|
pPackageOwner => 'CT_MRDS',
|
|
pPackageName => 'DATA_EXPORTER',
|
|
pPackageVersion => CT_MRDS.DATA_EXPORTER.PACKAGE_VERSION,
|
|
pPackageBuildDate => CT_MRDS.DATA_EXPORTER.PACKAGE_BUILD_DATE,
|
|
pPackageAuthor => CT_MRDS.DATA_EXPORTER.PACKAGE_AUTHOR
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('DATA_EXPORTER tracked successfully.');
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
DBMS_OUTPUT.PUT_LINE('ERROR tracking DATA_EXPORTER: ' || SQLERRM);
|
|
END;
|
|
/
|
|
|
|
-- Track FILE_ARCHIVER
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('Tracking FILE_ARCHIVER...');
|
|
CT_MRDS.ENV_MANAGER.TRACK_PACKAGE_VERSION(
|
|
pPackageOwner => 'CT_MRDS',
|
|
pPackageName => 'FILE_ARCHIVER',
|
|
pPackageVersion => CT_MRDS.FILE_ARCHIVER.PACKAGE_VERSION,
|
|
pPackageBuildDate => CT_MRDS.FILE_ARCHIVER.PACKAGE_BUILD_DATE,
|
|
pPackageAuthor => CT_MRDS.FILE_ARCHIVER.PACKAGE_AUTHOR
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('FILE_ARCHIVER tracked successfully.');
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
DBMS_OUTPUT.PUT_LINE('ERROR tracking FILE_ARCHIVER: ' || SQLERRM);
|
|
END;
|
|
/
|
|
|
|
-- Track WORKFLOW_MANAGER
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('Tracking WORKFLOW_MANAGER...');
|
|
CT_MRDS.ENV_MANAGER.TRACK_PACKAGE_VERSION(
|
|
pPackageOwner => 'CT_MRDS',
|
|
pPackageName => 'WORKFLOW_MANAGER',
|
|
pPackageVersion => CT_MRDS.WORKFLOW_MANAGER.PACKAGE_VERSION,
|
|
pPackageBuildDate => CT_MRDS.WORKFLOW_MANAGER.PACKAGE_BUILD_DATE,
|
|
pPackageAuthor => CT_MRDS.WORKFLOW_MANAGER.PACKAGE_AUTHOR
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('WORKFLOW_MANAGER tracked successfully.');
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
DBMS_OUTPUT.PUT_LINE('ERROR tracking WORKFLOW_MANAGER: ' || SQLERRM);
|
|
END;
|
|
/
|
|
|
|
-- Track FILE_MANAGER_ODS
|
|
BEGIN
|
|
DBMS_OUTPUT.PUT_LINE('Tracking FILE_MANAGER_ODS...');
|
|
CT_MRDS.ENV_MANAGER.TRACK_PACKAGE_VERSION(
|
|
pPackageOwner => 'ODS',
|
|
pPackageName => 'FILE_MANAGER_ODS',
|
|
pPackageVersion => ODS.FILE_MANAGER_ODS.PACKAGE_VERSION,
|
|
pPackageBuildDate => ODS.FILE_MANAGER_ODS.PACKAGE_BUILD_DATE,
|
|
pPackageAuthor => ODS.FILE_MANAGER_ODS.PACKAGE_AUTHOR
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('FILE_MANAGER_ODS tracked successfully.');
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
DBMS_OUTPUT.PUT_LINE('ERROR tracking FILE_MANAGER_ODS: ' || SQLERRM);
|
|
END;
|
|
/
|
|
|
|
PROMPT
|
|
PROMPT ====================================================================
|
|
PROMPT TRACKING SUMMARY - Last 10 Records
|
|
PROMPT ====================================================================
|
|
|
|
SELECT
|
|
PACKAGE_OWNER || '.' || PACKAGE_NAME AS PACKAGE,
|
|
PACKAGE_VERSION AS VERSION,
|
|
TO_CHAR(TRACKING_DATE, 'YYYY-MM-DD HH24:MI:SS') AS TRACKED_AT,
|
|
DETECTED_CHANGE_WITHOUT_VERSION AS CHANGE_NO_VER,
|
|
CASE
|
|
WHEN DETECTED_CHANGE_WITHOUT_VERSION = 'Y' THEN 'WARNING!'
|
|
ELSE 'OK'
|
|
END AS STATUS
|
|
FROM CT_MRDS.A_PACKAGE_VERSION_TRACKING
|
|
ORDER BY TRACKING_DATE DESC
|
|
FETCH FIRST 10 ROWS ONLY;
|
|
|
|
PROMPT
|
|
PROMPT ====================================================================
|
|
PROMPT TRACKING COMPLETE
|
|
PROMPT ====================================================================
|
|
PROMPT
|
|
PROMPT To check for changes in specific package:
|
|
PROMPT SELECT CT_MRDS.ENV_MANAGER.CHECK_PACKAGE_CHANGES('CT_MRDS', 'FILE_MANAGER') FROM DUAL;
|
|
PROMPT
|
|
PROMPT To get hash info for specific package:
|
|
PROMPT SELECT CT_MRDS.ENV_MANAGER.GET_PACKAGE_HASH_INFO('CT_MRDS', 'FILE_MANAGER') FROM DUAL;
|
|
PROMPT
|
|
PROMPT ====================================================================
|