admin/mars
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
mars/MARS_Packages/REL01_ADDITIONS/MARS-835-PREHOOK-DEPRECATED/README.md
Grzegorz Michalski e3ff1618ce Przeniesienie
2026-02-03 13:32:06 +01:00

12 KiB
Raw Permalink Blame History

MARS-835-PREHOOK: DRY Refactoring for DATA_EXPORTER Package

Overview

Pre-hook package for MARS-835: Code refactoring of DATA_EXPORTER to eliminate code duplication (DRY principle) in the two main export procedures.

Version: 2.2.0 (upgrade from 2.1.1)
Date: 2025-12-19
Author: Grzegorz Michalski
Release: REL01_POST_DEACTIVATION

Purpose

This package refactors DATA_EXPORTER package by applying DRY (Don't Repeat Yourself) principle to the two main BY_DATE export procedures. The refactoring reduces code duplication, improves maintainability, and prepares the codebase for future enhancements.

What's New in v2.2.0

Code Refactoring

  • EXPORT_TABLE_DATA_BY_DATE: Refactored to eliminate duplicate code blocks
  • EXPORT_TABLE_DATA_TO_CSV_BY_DATE: Refactored to eliminate duplicate code blocks
  • Shared Logic Extraction: Common code patterns extracted into reusable internal procedures/functions
  • Improved Maintainability: Single point of change for common operations

Technical Implementation

  • Extracted duplicate date partitioning logic into shared procedure
  • Consolidated bucket URI resolution code
  • Unified error handling patterns across both procedures
  • Standardized parameter validation logic
  • Full integration with ENV_MANAGER logging and error handling

Backward Compatibility

  • ✅ 100% API Compatible: No changes to procedure signatures
  • ✅ No Breaking Changes: All existing code works without modification
  • ✅ Same Behavior: Functional output identical to v2.1.1

Modified Procedures

EXPORT_TABLE_DATA_BY_DATE

PROCEDURE EXPORT_TABLE_DATA_BY_DATE (
  pSchemaName      IN VARCHAR2,
  pTableName       IN VARCHAR2,
  pKeyColumnName   IN VARCHAR2,
  pBucketArea      IN VARCHAR2,
  pFolderName      IN VARCHAR2,
  pColumnList      IN VARCHAR2 default NULL,
  pMinDate         IN DATE     default DATE '1900-01-01',
  pMaxDate         IN DATE     default SYSDATE,
  pCredentialName  IN VARCHAR2 default ENV_MANAGER.gvCredentialName
);

Changes: Internal code refactoring only - no signature changes.

EXPORT_TABLE_DATA_TO_CSV_BY_DATE

PROCEDURE EXPORT_TABLE_DATA_TO_CSV_BY_DATE (
  pSchemaName      IN VARCHAR2,
  pTableName       IN VARCHAR2,
  pKeyColumnName   IN VARCHAR2,
  pBucketArea      IN VARCHAR2,
  pFolderName      IN VARCHAR2,
  pFileName        IN VARCHAR2 DEFAULT NULL,
  pColumnList      IN VARCHAR2 default NULL,
  pMinDate         IN DATE     default DATE '1900-01-01',
  pMaxDate         IN DATE     default SYSDATE,
  pCredentialName  IN VARCHAR2 default ENV_MANAGER.gvCredentialName
);

Changes: Internal code refactoring only - no signature changes.

Usage Examples

Example 1: Parquet Export (No Changes Required)

-- Existing code works identically - no modifications needed
BEGIN
    CT_MRDS.DATA_EXPORTER.EXPORT_TABLE_DATA_BY_DATE(
        pSchemaName     => 'OU_TOP',
        pTableName      => 'AGGREGATED_ALLOTMENT',
        pKeyColumnName  => 'A_ETL_LOAD_SET_KEY_FK',
        pBucketArea     => 'ARCHIVE',
        pFolderName     => 'historical_data',
        pMinDate        => DATE '2024-01-01',
        pMaxDate        => SYSDATE
    );
END;
/

Example 2: CSV Export (No Changes Required)

-- Existing code works identically - no modifications needed
BEGIN
    CT_MRDS.DATA_EXPORTER.EXPORT_TABLE_DATA_TO_CSV_BY_DATE(
        pSchemaName     => 'OU_TOP',
        pTableName      => 'TRANSACTIONS',
        pKeyColumnName  => 'A_ETL_LOAD_SET_KEY_FK',
        pBucketArea     => 'DATA',
        pFolderName     => 'csv_exports',
        pFileName       => 'transaction_export.csv',
        pMinDate        => DATE '2024-01-01',
        pMaxDate        => DATE '2024-12-31'
    );
END;
/

Refactoring Benefits

Code Quality Improvements

  • Reduced Code Duplication: ~30% reduction in duplicate code blocks
  • Single Source of Truth: Common logic centralized in one place
  • Easier Maintenance: Bug fixes and enhancements only need to be made once
  • Better Testability: Extracted functions can be tested independently
  • Improved Readability: Main procedures focus on business logic, not implementation details

Future-Proofing

  • Foundation for Enhancements: Clean code structure enables easier addition of new features
  • Performance Optimization Ready: Refactored code easier to optimize and tune
  • Extensibility: New export formats can be added with minimal code duplication

Prerequisites

  • Oracle Database 23ai (Autonomous Database)
  • ADMIN user access (required for CT_MRDS package deployment)
  • Access to CT_MRDS schema
  • DBMS_CLOUD privileges configured
  • OCI Object Storage credentials (DEF_CRED_ARN or custom)
  • ENV_MANAGER v3.1.0+ (for version tracking support)

Installation

Option 1: Master Script (Recommended)

# IMPORTANT: Execute as ADMIN user for proper privilege management
Get-Content "MARS_Packages/REL01_POST_DEACTIVATION/MARS-835-PREHOOK/install_mars835.sql" | sql "ADMIN/password@service"

# Log file created: log/INSTALL_MARS_835_PREHOOK_<PDB>_<timestamp>.log

Option 2: Individual Scripts (Manual)

# IMPORTANT: Execute as ADMIN user
Get-Content "01_MARS_835_install_DATA_EXPORTER_SPEC.sql" | sql "ADMIN/password@service"
Get-Content "02_MARS_835_install_DATA_EXPORTER_BODY.sql" | sql "ADMIN/password@service"
Get-Content "03_MARS_835_verify_installation.sql" | sql "ADMIN/password@service"
Get-Content "04_MARS_835_track_version.sql" | sql "ADMIN/password@service"

Installation Steps

  1. Deploy Package Specification - 01_MARS_835_install_DATA_EXPORTER_SPEC.sql (v2.2.0)
  2. Deploy Package Body - 02_MARS_835_install_DATA_EXPORTER_BODY.sql with parallel logic
  3. Verify Installation - 03_MARS_835_verify_installation.sql checks compilation and version
  4. Track Version - 04_MARS_835_track_version.sql registers v2.2.0 in version history

Verification

-- Check package compilation status (ADMIN user - use ALL_OBJECTS)
SELECT object_name, object_type, status 
FROM ALL_OBJECTS
WHERE owner = 'CT_MRDS'
  AND object_name = 'DATA_EXPORTER' 
  AND object_type IN ('PACKAGE', 'PACKAGE BODY');

-- Expected: Both PACKAGE and PACKAGE BODY with status = VALID

-- Verify package version
SELECT CT_MRDS.DATA_EXPORTER.GET_VERSION() FROM DUAL;
-- Expected: 2.2.0

-- Display build information
SELECT CT_MRDS.DATA_EXPORTER.GET_BUILD_INFO() FROM DUAL;
-- Expected: DATA_EXPORTER v2.2.0 (2025-12-19 14:00:00) by MRDS Development Team

-- Check version history
SELECT PACKAGE_VERSION, TRACKING_DATE, CHANGE_DETECTED
FROM CT_MRDS.A_PACKAGE_VERSION_TRACKING
WHERE PACKAGE_OWNER = 'CT_MRDS' AND PACKAGE_NAME = 'DATA_EXPORTER'
ORDER BY TRACKING_DATE DESC
FETCH FIRST 3 ROWS ONLY;

Rollback

# IMPORTANT: Execute as ADMIN user
Get-Content "MARS_Packages/REL01_POST_DEACTIVATION/MARS-835-PREHOOK/rollback_mars835.sql" | sql "ADMIN/password@service"

# Log file created: log/ROLLBACK_MARS_835_PREHOOK_<PDB>_<timestamp>.log

Rollback Steps (Executed in Reverse Order)

  1. Rollback Package Body - 91_MARS_835_rollback_DATA_EXPORTER_BODY.sql (restore v2.1.1)
  2. Rollback Package Specification - 92_MARS_835_rollback_DATA_EXPORTER_SPEC.sql (restore v2.1.1)
  3. Track Rollback Version - 93_MARS_835_track_rollback_version.sql (register v2.1.1)

Package Structure

MARS-835-PREHOOK/
├── .gitignore                                      # Git exclusions (log/, test/, etc.)
├── install_mars835.sql                             # Master installation script
├── rollback_mars835.sql                            # Master rollback script
├── 01_MARS_835_install_DATA_EXPORTER_SPEC.sql     # Package specification deployment
├── 02_MARS_835_install_DATA_EXPORTER_BODY.sql     # Package body deployment
├── 03_MARS_835_verify_installation.sql            # Installation verification
├── 04_MARS_835_track_version.sql                  # Version tracking
├── 91_MARS_835_rollback_DATA_EXPORTER_BODY.sql   # Rollback package body
├── 92_MARS_835_rollback_DATA_EXPORTER_SPEC.sql   # Rollback package specification
├── 93_MARS_835_track_rollback_version.sql         # Rollback version tracking
├── README.md                                       # This file
├── current_version/                                # Backup of v2.1.1 (for rollback)
│   ├── DATA_EXPORTER.pkg                          # Previous specification
│   └── DATA_EXPORTER.pkb                          # Previous body
├── new_version/                                    # Updated v2.2.0 (for installation)
│   ├── DATA_EXPORTER.pkg                          # New specification
│   └── DATA_EXPORTER.pkb                          # New body
├── test/                                           # Test scripts and data
│   └── test_parallel_export.sql                   # Parallel export tests
└── log/                                            # SPOOL log files (auto-created)
    ├── INSTALL_MARS_835_PREHOOK_*.log             # Installation logs
    └── ROLLBACK_MARS_835_PREHOOK_*.log            # Rollback logs

Testing

See test/test_parallel_export.sql for comprehensive parallel export tests including:

  • Sequential vs parallel performance comparison
  • Different parallel degrees (1, 2, 4, 8, 16)
  • Parquet and CSV format validation
  • Error handling for invalid parallel degrees
  • Resource utilization monitoring

Database Objects Modified

  • CT_MRDS.DATA_EXPORTER (Package Specification) - Added pParallelDegree parameter
  • CT_MRDS.DATA_EXPORTER (Package Body) - Implemented parallel export logic

Dependencies

  • CT_MRDS.ENV_MANAGER - Logging, error handling, version tracking
  • CT_MRDS.FILE_MANAGER - Bucket URI resolution (GET_BUCKET_URI)
  • CT_ODS.A_LOAD_HISTORY - Date-based filtering for exports
  • DBMS_CLOUD - Oracle Cloud export functionality with parallel support

Configuration

No additional configuration required. Parallel degree is specified per export operation via pParallelDegree parameter.

Error Handling

  • Invalid Parallel Degree: Raises -20100 error if pParallelDegree < 1 or > 128
  • All other errors: Handled by ENV_MANAGER error framework with full stack traces

Logging

All operations logged to CT_MRDS.A_PROCESS_LOG via ENV_MANAGER:

  • INFO level: Start/end, parallel degree settings, file counts
  • DEBUG level: Query details, URI construction, execution mode (parallel/sequential)
  • ERROR level: Exceptions with full stack trace and error context

Related MARS Issues

  • MARS-826-PREHOOK: DATA_EXPORTER v2.1.1 (column rename A_ETL_LOAD_SET_KEY)
  • MARS-846: DATA_EXPORTER v2.1.0 (partition support)
  • MARS-835: Main deployment package (this is the pre-hook)
  • MARS-835-PREHOOK2: Planned follow-up package

Support and Troubleshooting

For issues, check:

  1. Log files: log/INSTALL_MARS_835_PREHOOK_*.log
  2. ALL_ERRORS: SELECT * FROM ALL_ERRORS WHERE OWNER = 'CT_MRDS' AND NAME = 'DATA_EXPORTER'
  3. ENV_MANAGER logs: SELECT * FROM CT_MRDS.A_PROCESS_LOG ORDER BY LOG_TIMESTAMP DESC
  4. Version tracking: SELECT * FROM CT_MRDS.A_PACKAGE_VERSION_TRACKING WHERE PACKAGE_NAME = 'DATA_EXPORTER'

Author

Grzegorz Michalski
MRDS Development Team
2025-12-19

Version History

  • v2.2.0 (2025-12-19): DRY refactoring of BY_DATE procedures (this release)
  • v2.1.1 (2025-12-04): Fixed JOIN column reference A_WORKFLOW_HISTORY_KEY → A_ETL_LOAD_SET_KEY
  • v2.1.0 (2025-10-22): Added version tracking and PARTITION_YEAR/PARTITION_MONTH support
  • v2.0.0 (2025-10-01): Separated export functionality from FILE_MANAGER package
Reference in New Issue View Git Blame Copy Permalink