mars/MARS_Packages/REL02/MARS-1057/README.md

# MARS-1057: Batch External Table Creation

## Overview
This MARS package adds batch external table creation capabilities to the FILE_MANAGER package, enabling automatic creation of external table sets (INBOX, ODS, ARCHIVE) based on A_SOURCE_FILE_CONFIG metadata.

**Jira Issue:** MARS-1057  
**Package Version:** FILE_MANAGER 3.4.0  
**Author:** Grzegorz Michalski  
**Date:** 2025-11-27

## Contents
- `install_mars1057.sql` - Master installation script with SPOOL logging
- `rollback_mars1057.sql` - Master rollback script
- `01_MARS_1057_install_CT_MRDS_FILE_MANAGER_SPEC.sql` - Install package specification
- `02_MARS_1057_install_CT_MRDS_FILE_MANAGER_BODY.sql` - Install package body
- `91_MARS_1057_rollback_CT_MRDS_FILE_MANAGER_BODY.sql` - Rollback package body
- `92_MARS_1057_rollback_CT_MRDS_FILE_MANAGER_SPEC.sql` - Rollback package specification
- `track_package_versions.sql` - Universal package version tracking
- `verify_packages_version.sql` - Universal package verification
- `current_version/` - FILE_MANAGER v3.3.0 (before MARS-1057)
- `new_version/` - FILE_MANAGER v3.4.0 (after MARS-1057)
- `.gitignore` - Git exclusions for temporary files

## Prerequisites
- Oracle Database 23ai
- FILE_MANAGER package v3.3.0 installed
- ENV_MANAGER package v3.1.0+ with version tracking
- ADMIN user access for deployment
- ODS.FILE_MANAGER_ODS package available

## New Features

### 1. CREATE_EXTERNAL_TABLES_SET
Creates a complete set of 3 external tables (INBOX, ODS, ARCHIVE) for a single configuration from A_SOURCE_FILE_CONFIG.

**Signature:**
```sql
PROCEDURE CREATE_EXTERNAL_TABLES_SET (
    pSourceFileConfigKey IN NUMBER,
    pRecreate            IN BOOLEAN DEFAULT FALSE
);
```

**Example:**
```sql
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_SET(
        pSourceFileConfigKey => 123,
        pRecreate => FALSE
    );
END;
/
```

**Features:**
- Automatic table naming: `{TABLE_ID}_{INBOX|ODS|ARCHIVE}`
- Official path patterns compliance
- Optional drop and recreate
- Full ENV_MANAGER logging
- Error handling with detailed messages

### 2. CREATE_EXTERNAL_TABLES_BATCH
Creates external table sets for multiple configurations based on filter criteria.

**Signature:**
```sql
PROCEDURE CREATE_EXTERNAL_TABLES_BATCH (
    pSourceKey      IN VARCHAR2 DEFAULT NULL,
    pSourceFileId   IN VARCHAR2 DEFAULT NULL,
    pTableId        IN VARCHAR2 DEFAULT NULL,
    pRecreate       IN BOOLEAN DEFAULT FALSE
);
```

**Examples:**
```sql
-- All external tables for C2D source
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_BATCH(
        pSourceKey => 'C2D'
    );
END;
/

-- Recreate all external tables
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_BATCH(
        pRecreate => TRUE
    );
END;
/

-- Specific table across all sources
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_BATCH(
        pTableId => 'A_UC_DISSEM_METADATA_LOADS'
    );
END;
/
```

**Features:**
- Filters only INPUT type files
- Continues processing on errors
- Returns summary (Total/Processed/Failed)
- Comprehensive logging

## Installation

### Option 1: Master Script (Recommended)
```powershell
# IMPORTANT: Execute as ADMIN user
Get-Content "MARS_Packages/REL01/MARS-1057/install_mars1057.sql" | sql "ADMIN/password@service"

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

**Installation Steps:**
1. Install FILE_MANAGER package specification v3.4.0
2. Install FILE_MANAGER package body v3.4.0
3. Track version in A_PACKAGE_VERSION_TRACKING
4. Verify all tracked packages for untracked changes

### Option 2: Individual Scripts
```powershell
# IMPORTANT: Execute as ADMIN user
Get-Content "01_MARS_1057_install_CT_MRDS_FILE_MANAGER_SPEC.sql" | sql "ADMIN/password@service"
Get-Content "02_MARS_1057_install_CT_MRDS_FILE_MANAGER_BODY.sql" | sql "ADMIN/password@service"
Get-Content "track_package_versions.sql" | sql "ADMIN/password@service"
Get-Content "verify_packages_version.sql" | sql "ADMIN/password@service"
```

## Verification
```sql
-- Check package version
SELECT CT_MRDS.FILE_MANAGER.GET_VERSION() FROM DUAL;
-- Expected: 3.4.0

-- Check for errors (ADMIN user checks specific schema)
SELECT * FROM ALL_ERRORS 
WHERE OWNER = 'CT_MRDS' AND NAME = 'FILE_MANAGER';
-- Expected: No rows

-- Verify new procedures exist
SELECT procedure_name
FROM ALL_PROCEDURES
WHERE OWNER = 'CT_MRDS'
  AND object_name = 'FILE_MANAGER'
  AND procedure_name IN ('CREATE_EXTERNAL_TABLES_SET', 'CREATE_EXTERNAL_TABLES_BATCH');
-- Expected: 2 rows

-- Check for untracked changes
SELECT CT_MRDS.ENV_MANAGER.CHECK_PACKAGE_CHANGES('CT_MRDS', 'FILE_MANAGER') FROM DUAL;
-- Expected: OK: Package CT_MRDS.FILE_MANAGER has not changed.
```

## Rollback
```powershell
# IMPORTANT: Execute as ADMIN user
Get-Content "MARS_Packages/REL01/MARS-1057/rollback_mars1057.sql" | sql "ADMIN/password@service"
```

**Rollback restores:**
- FILE_MANAGER package specification v3.3.0
- FILE_MANAGER package body v3.3.0
- Removes CREATE_EXTERNAL_TABLES_SET procedure
- Removes CREATE_EXTERNAL_TABLES_BATCH procedure

## Expected Changes
- **FILE_MANAGER package**: v3.3.0 → v3.4.0
- **New procedures**: CREATE_EXTERNAL_TABLES_SET, CREATE_EXTERNAL_TABLES_BATCH
- **SPEC size**: +3.3 KB (declaration and documentation)
- **BODY size**: +8.6 KB (implementation with logging)

## Testing

### Test 1: Create Single Set
```sql
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_SET(
        pSourceFileConfigKey => 123
    );
END;
/

-- Verify tables created
SELECT table_name 
FROM ALL_TABLES 
WHERE OWNER = 'ODS' 
  AND table_name LIKE '%_INBOX' 
   OR table_name LIKE '%_ODS' 
   OR table_name LIKE '%_ARCHIVE';
```

### Test 2: Batch Creation
```sql
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_BATCH(
        pSourceKey => 'C2D'
    );
END;
/

-- Check process log for results
SELECT * 
FROM CT_MRDS.A_PROCESS_LOG 
WHERE LOG_TIMESTAMP > SYSDATE - INTERVAL '1' HOUR
  AND PROCEDURE_NAME LIKE '%CREATE_EXTERNAL_TABLES%'
ORDER BY LOG_TIMESTAMP DESC;
```

### Test 3: Recreate Existing Tables
```sql
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_SET(
        pSourceFileConfigKey => 123,
        pRecreate => TRUE
    );
END;
/
```

## Usage Examples

### Example 1: Setup All External Tables for New Source
```sql
-- 1. Add source configuration
CALL FILE_MANAGER.ADD_SOURCE('LM', 'Liquidity Management');

-- 2. Add file configurations
CALL FILE_MANAGER.ADD_SOURCE_FILE_CONFIG(
    pSourceKey => 'LM',
    pSourceFileType => 'INPUT',
    pSourceFileId => 'STANDING_FACILITIES',
    pSourceFileDesc => 'Standing Facilities Data',
    pSourceFileNamePattern => 'SF_*.csv',
    pTableId => 'STANDING_FACILITIES',
    pTemplateTableName => 'CT_ET_TEMPLATES.LM_STANDING_FACILITIES',
    pEncoding => 'UTF8'
);

-- 3. Create all external tables for this source
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_BATCH(
        pSourceKey => 'LM'
    );
END;
/
```

### Example 2: Recreate All External Tables
```sql
-- Useful after bucket URI changes or template table modifications
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_BATCH(
        pRecreate => TRUE
    );
END;
/
```

### Example 3: Create Tables for Specific File Type
```sql
BEGIN
    FILE_MANAGER.CREATE_EXTERNAL_TABLES_BATCH(
        pSourceFileId => 'UC_DISSEM'
    );
END;
/
```

## Known Issues
None

## Dependencies
- **CT_MRDS.ENV_MANAGER** - Logging and error handling
- **ODS.FILE_MANAGER_ODS** - AUTHID DEFINER wrapper for external table creation
- **CT_MRDS.A_SOURCE_FILE_CONFIG** - Source file configuration metadata
- **CT_ET_TEMPLATES schema** - Template table definitions

## Related
- **MARS-1056** - VARCHAR2 CHAR/BYTE semantics fix
- **MARS-1049** - CSV encoding support
- **Package Deployment Guide** - Standard deployment procedures
- **Tables Setup Guide** - External table configuration guide

## Notes
- All installations must be executed as ADMIN user
- Use `ALL_*` views instead of `USER_*` views for verification
- Master scripts include SPOOL logging for audit trail
- ACCEPT validation prevents accidental execution
- Follows official path patterns: INBOX (3-level), ODS (2-level), ARCHIVE (2-level)