Test

CREATE OR REPLACE PROCEDURE compare_tables(cur_tables IN VARCHAR2) AS
    l_sp_name            VARCHAR2(50) := 'COMPARE_TABLES';
    sql_stmt             VARCHAR2(4000);
    merge_stmt           CLOB;
    ip_merge_stmt_1      CLOB;
    ip_merge_stmt_2      CLOB;
    ip_tbl_last_update_col VARCHAR2(1000);
    ip_tbl_create_date_col VARCHAR2(1000);
    ip_tbl_primary_col   VARCHAR2(1000);
    REC_TABLE            tbl_str; -- Custom type for tracking PKs
    batch_size           NUMBER := 500000;
    total_rows           NUMBER := 0;
    batch_start          NUMBER := 1;
    batch_end            NUMBER;
    batch_number         NUMBER := 1;
    l_error_message      VARCHAR2(3000);
BEGIN
    -- Get metadata from mapper
    SELECT last_update_date, create_date, primary_key, merge_stmt_1, merge_stmt_2
    INTO ip_tbl_last_update_col, ip_tbl_create_date_col, ip_tbl_primary_col, ip_merge_stmt_1, ip_merge_stmt_2
    FROM temp_date_col_mapper
    WHERE table_name = cur_tables;

    -- Get total row count
    sql_stmt := 'SELECT COUNT(*) FROM cr_5_0_x.' || cur_tables;
    EXECUTE IMMEDIATE sql_stmt INTO total_rows;

    -- Batch loop
    WHILE batch_start <= total_rows LOOP
        batch_end := batch_start + batch_size - 1;
        IF batch_end > total_rows THEN
            batch_end := total_rows;
        END IF;

        -------------------------------------------------------------------
        -- Reconciliation Logging: INSERT rows
        -------------------------------------------------------------------
        sql_stmt := 'INSERT INTO reconciliation_log_archive (table_name, primary_key_val, action_type, action_time)
                     SELECT ''' || cur_tables || ''', src.' || ip_tbl_primary_col || ', ''INSERT'', SYSTIMESTAMP
                     FROM (
                         SELECT a.*, ROWNUM AS rn FROM cr_5_0_x.' || cur_tables || ' a
                     ) src
                     WHERE src.rn BETWEEN ' || batch_start || ' AND ' || batch_end || '
                     AND NOT EXISTS (
                         SELECT 1 FROM ' || cur_tables || ' tgt
                         WHERE tgt.' || ip_tbl_primary_col || ' = src.' || ip_tbl_primary_col || '
                     )';
        EXECUTE IMMEDIATE sql_stmt;

        -------------------------------------------------------------------
        -- Reconciliation Logging: UPDATE rows
        -------------------------------------------------------------------
        sql_stmt := 'INSERT INTO reconciliation_log_archive (table_name, primary_key_val, action_type, action_time)
                     SELECT ''' || cur_tables || ''', src.' || ip_tbl_primary_col || ', ''UPDATE'', SYSTIMESTAMP
                     FROM (
                         SELECT a.*, ROWNUM AS rn FROM cr_5_0_x.' || cur_tables || ' a
                     ) src
                     JOIN ' || cur_tables || ' tgt
                     ON tgt.' || ip_tbl_primary_col || ' = src.' || ip_tbl_primary_col || '
                     WHERE src.rn BETWEEN ' || batch_start || ' AND ' || batch_end || '
                     AND (' || ip_merge_stmt_1 || ') != (' || ip_merge_stmt_2 || ')';
        EXECUTE IMMEDIATE sql_stmt;

        -------------------------------------------------------------------
        -- Build and execute your original MERGE
        -------------------------------------------------------------------
        merge_stmt := ip_merge_stmt_1 ||
                      TO_CLOB(' WHERE rn BETWEEN ') ||
                      TO_CLOB(batch_start) ||
                      TO_CLOB(' AND ') ||
                      TO_CLOB(batch_end) ||
                      TO_CLOB(') src ') ||
                      ip_merge_stmt_2;

        DBMS_OUTPUT.PUT_LINE('Batch from ' || batch_start || ' to ' || batch_end);
        DBMS_OUTPUT.PUT_LINE('MERGE STMT: ' || merge_stmt);

        EXECUTE IMMEDIATE merge_stmt;

        -- Optional: Returning primary keys
        -- EXECUTE IMMEDIATE merge_stmt RETURNING BULK COLLECT INTO REC_TABLE;

        batch_start := batch_end + 1;
        batch_number := batch_number + 1;
        COMMIT;
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error occurred in batch: ' || batch_number);
        dbms_output.put_line('Stack: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
        dbms_output.put_line('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        ROLLBACK;
        l_error_message := DBMS_UTILITY.format_error_backtrace || DBMS_UTILITY.format_error_stack;
        sp_ael_arch_error_log(l_sp_name, cur_tables, batch_number, batch_start, l_error_message);
END compare_tables;
/