Oracle to PostgreSQL Migration — Zero Rows for Three Days

-- ============================================================
-- COMPLETE ORACLE TO POSTGRESQL EXCEPTION NAME MAPPING
-- ============================================================

-- GROUP 1: Direct name equivalents (same name, both databases)
-- Oracle: NO_DATA_FOUND   -> PostgreSQL: NO_DATA_FOUND
-- Oracle: TOO_MANY_ROWS   -> PostgreSQL: TOO_MANY_ROWS
-- Oracle: ZERO_DIVIDE     -> PostgreSQL: division_by_zero
-- Oracle: CURSOR_ALREADY_OPEN -> PostgreSQL: No equivalent (cursors work differently)

-- GROUP 2: Name changes, same semantics
-- Oracle: DUP_VAL_ON_INDEX -> PostgreSQL: unique_violation (SQLSTATE 23505)
-- Oracle: VALUE_ERROR      -> PostgreSQL: varies:
--   Overflow:          numeric_value_out_of_range (SQLSTATE 22003)
--   Cast failure:      invalid_text_representation (SQLSTATE 22P02)
--   String too long:   string_data_right_truncation (SQLSTATE 22001)
-- Oracle: INVALID_NUMBER   -> PostgreSQL: invalid_text_representation (SQLSTATE 22P02)
-- Oracle: NOT_LOGGED_ON    -> PostgreSQL: connection_failure (SQLSTATE 08006)

-- GROUP 3: Oracle exceptions with no direct PostgreSQL equivalent
-- Oracle: ORA-06512 (call stack frame) -> Must use GET STACKED DIAGNOSTICS
-- Oracle: PRAGMA AUTONOMOUS_TRANSACTION -> dblink or pg_background extension
-- Oracle: RAISE_APPLICATION_ERROR       -> RAISE EXCEPTION with SQLSTATE 'P0001'

-- ============================================================
-- EXCEPTION HANDLER TRANSLATION EXAMPLES
-- ============================================================

-- ORACLE:
CREATE OR REPLACE PROCEDURE process_payment(p_account_id NUMBER) AS
BEGIN
  UPDATE accounts SET balance = balance - 100
  WHERE account_id = p_account_id;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE NO_DATA_FOUND;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Account not found: account_id=' || p_account_id);
  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(-20002,
      'Duplicate payment detected: account_id=' || p_account_id);
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20099,
      'Unexpected error: ' || SQLERRM, TRUE);
END;
/

-- POSTGRESQL EQUIVALENT:
CREATE OR REPLACE FUNCTION io.thecodeforge.process_payment(
  p_account_id INTEGER
) RETURNS VOID AS $$
DECLARE
  v_affected   INTEGER;
  v_sqlstate   TEXT;
  v_message    TEXT;
  v_detail     TEXT;
  v_context    TEXT;
  v_constraint TEXT;
BEGIN
  UPDATE accounts
  SET balance = balance - 100
  WHERE account_id = p_account_id;

  GET DIAGNOSTICS v_affected = ROW_COUNT;

  IF v_affected = 0 THEN
    RAISE EXCEPTION 'Account not found: account_id=%', p_account_id
      USING ERRCODE = 'P0002';  -- no_data_found in the user range
  END IF;

EXCEPTION
  WHEN no_data_found THEN
    -- Raised above or by a SELECT INTO that returned no rows
    RAISE EXCEPTION 'Account not found: account_id=%', p_account_id
      USING ERRCODE = 'P0001';

  WHEN unique_violation THEN
    -- PostgreSQL equivalent of Oracle's DUP_VAL_ON_INDEX
    GET STACKED DIAGNOSTICS
      v_constraint = CONSTRAINT_NAME,
      v_detail     = PG_EXCEPTION_DETAIL;
    RAISE EXCEPTION 'Duplicate payment detected: account_id=%, constraint=%',
        p_account_id, v_constraint
      USING ERRCODE = 'P0001',
            DETAIL  = v_detail;

  WHEN OTHERS THEN
    -- Catch-all: capture full context before re-raising
    GET STACKED DIAGNOSTICS
      v_sqlstate = RETURNED_SQLSTATE,
      v_message  = MESSAGE_TEXT,
      v_detail   = PG_EXCEPTION_DETAIL,
      v_context  = PG_EXCEPTION_CONTEXT;

    -- Log to error table for post-mortem
    INSERT INTO io.thecodeforge.error_log
      (procedure_name, parameters, sqlstate, message, detail, context, logged_at)
    VALUES
      ('process_payment', 'account_id=' || p_account_id,
       v_sqlstate, v_message, v_detail, v_context, NOW());

    RAISE EXCEPTION 'Payment processing failed: account_id=%, error=%',
        p_account_id, v_message
      USING ERRCODE   = v_sqlstate,
            DETAIL    = v_context;
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- CENTRALIZED ERROR LOGGING INFRASTRUCTURE
-- ============================================================

-- Step 1: Create the error log table
CREATE TABLE IF NOT EXISTS io.thecodeforge.error_log (
  log_id         BIGSERIAL    PRIMARY KEY,
  procedure_name TEXT         NOT NULL,
  parameters     TEXT,
  sqlstate       CHAR(5),
  message        TEXT,
  detail         TEXT,
  hint           TEXT,
  context        TEXT,
  constraint_name TEXT,
  session_user   TEXT         DEFAULT SESSION_USER,
  application_name TEXT       DEFAULT current_setting('application_name'),
  logged_at      TIMESTAMPTZ  DEFAULT NOW()
);

CREATE INDEX idx_error_log_logged_at
  ON io.thecodeforge.error_log(logged_at DESC);
CREATE INDEX idx_error_log_procedure
  ON io.thecodeforge.error_log(procedure_name, logged_at DESC);

-- Step 2: Centralized log function
-- Note: This function runs OUTSIDE a transaction via dblink
-- so it persists even when the calling transaction rolls back
CREATE OR REPLACE FUNCTION io.thecodeforge.log_error(
  p_procedure   TEXT,
  p_parameters  TEXT,
  p_sqlstate    TEXT,
  p_message     TEXT,
  p_detail      TEXT DEFAULT NULL,
  p_context     TEXT DEFAULT NULL,
  p_constraint  TEXT DEFAULT NULL
) RETURNS VOID AS $$
DECLARE
  v_conn TEXT;
BEGIN
  -- Use dblink to write outside the current transaction
  -- so the log entry survives a rollback
  PERFORM dblink_connect(
    'io_error_log',
    format('dbname=%s user=%s password=%s',
      current_database(), SESSION_USER,
      current_setting('app.db_password', true)
    )
  );

  PERFORM dblink_exec('io_error_log', format(
    'INSERT INTO io.thecodeforge.error_log '
    '(procedure_name, parameters, sqlstate, message, detail, context, constraint_name) '
    'VALUES (%L, %L, %L, %L, %L, %L, %L)',
    p_procedure, p_parameters, p_sqlstate,
    p_message, p_detail, p_context, p_constraint
  ));

  PERFORM dblink_disconnect('io_error_log');
EXCEPTION WHEN OTHERS THEN
  -- If the log write fails, fall back to server log
  -- Never let logging failure hide the original error
  RAISE LOG 'Error log write failed for procedure=%: sqlstate=%, message=%',
    p_procedure, p_sqlstate, p_message;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Step 3: Pattern for any migrated procedure
CREATE OR REPLACE FUNCTION io.thecodeforge.process_order(
  p_order_id INTEGER
) RETURNS VOID AS $$
DECLARE
  v_sqlstate       TEXT;
  v_message        TEXT;
  v_detail         TEXT;
  v_hint           TEXT;
  v_context        TEXT;
  v_constraint     TEXT;
  v_customer_id    INTEGER;
  v_order_amount   NUMERIC;
BEGIN
  -- Log entry for tracing (equivalent to Oracle's DBMS_OUTPUT or debug logging)
  RAISE DEBUG 'process_order: starting for order_id=%', p_order_id;

  -- Business logic
  SELECT customer_id, total_amount
  INTO STRICT v_customer_id, v_order_amount
  FROM orders
  WHERE order_id = p_order_id;
  -- STRICT makes SELECT INTO raise NO_DATA_FOUND or TOO_MANY_ROWS
  -- just like Oracle's SELECT INTO behavior

  RAISE DEBUG 'process_order: customer_id=%, amount=%',
    v_customer_id, v_order_amount;

  -- ... rest of business logic ...

EXCEPTION
  WHEN no_data_found THEN
    PERFORM io.thecodeforge.log_error(
      'process_order',
      'order_id=' || p_order_id,
      'P0002', 'Order not found',
      NULL, NULL, NULL
    );
    RAISE EXCEPTION 'Order not found: order_id=%', p_order_id
      USING ERRCODE = 'P0002';

  WHEN too_many_rows THEN
    PERFORM io.thecodeforge.log_error(
      'process_order',
      'order_id=' || p_order_id,
      '21000', 'Ambiguous order ID — multiple rows found',
      NULL, NULL, NULL
    );
    RAISE EXCEPTION 'Ambiguous order: order_id=% matches multiple rows', p_order_id
      USING ERRCODE = '21000';

  WHEN OTHERS THEN
    -- Capture the full context — this is your ORA-06512 replacement
    GET STACKED DIAGNOSTICS
      v_sqlstate   = RETURNED_SQLSTATE,
      v_message    = MESSAGE_TEXT,
      v_detail     = PG_EXCEPTION_DETAIL,
      v_hint       = PG_EXCEPTION_HINT,
      v_context    = PG_EXCEPTION_CONTEXT,
      v_constraint = CONSTRAINT_NAME;

    -- Log immediately before re-raise
    PERFORM io.thecodeforge.log_error(
      'process_order',
      'order_id=' || p_order_id,
      v_sqlstate, v_message,
      v_detail, v_context, v_constraint
    );

    -- Re-raise with enriched message for the caller
    RAISE EXCEPTION 'Order processing failed: order_id=%, error=%, sqlstate=%',
        p_order_id, v_message, v_sqlstate
      USING ERRCODE = v_sqlstate,
            DETAIL  = v_context;
END;
$$ LANGUAGE plpgsql;

-- Step 4: Query the error log during incident response
SELECT
  logged_at,
  procedure_name,
  parameters,
  sqlstate,
  message,
  LEFT(context, 500) AS context_preview,
  constraint_name
FROM io.thecodeforge.error_log
WHERE logged_at > NOW() - INTERVAL '1 hour'
ORDER BY logged_at DESC
LIMIT 50;

-- ============================================================
-- ORACLE PATTERN: Implicit transaction with partial rollback
-- ============================================================
-- Oracle: transaction stays active even after a handled exception
-- The audit INSERT is committed even if the UPDATE fails
CREATE OR REPLACE PROCEDURE oracle_pattern(p_account_id NUMBER) AS
BEGIN
  -- This INSERT is part of the current transaction
  INSERT INTO audit_log (event, account_id, logged_at)
  VALUES ('PAYMENT_ATTEMPT', p_account_id, SYSDATE);

  SAVEPOINT before_debit;

  -- This UPDATE may fail
  UPDATE accounts
  SET balance = balance - 100
  WHERE account_id = p_account_id;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO before_debit;   -- Only rolls back the UPDATE
    -- The audit_log INSERT is preserved (still in the transaction)
    COMMIT;                      -- Commits the audit entry
    RAISE;                       -- Re-raises the original exception
END;
/

-- ============================================================
-- POSTGRESQL EQUIVALENT: Explicit savepoint with dblink audit
-- ============================================================
CREATE OR REPLACE FUNCTION io.thecodeforge.postgres_pattern(
  p_account_id INTEGER
) RETURNS VOID AS $$
DECLARE
  v_sqlstate TEXT;
  v_message  TEXT;
  v_context  TEXT;
BEGIN
  -- PostgreSQL: savepoint for partial rollback
  SAVEPOINT before_debit;

  UPDATE accounts
  SET balance = balance - 100
  WHERE account_id = p_account_id;

  -- If we get here, the update succeeded
  -- Write audit log AFTER success, inside the same transaction
  INSERT INTO audit_log (event, account_id, logged_at)
  VALUES ('PAYMENT_SUCCESS', p_account_id, NOW());

EXCEPTION
  WHEN OTHERS THEN
    -- Roll back only the UPDATE (and anything after the savepoint)
    ROLLBACK TO SAVEPOINT before_debit;

    -- Use GET STACKED DIAGNOSTICS before any further SQL
    GET STACKED DIAGNOSTICS
      v_sqlstate = RETURNED_SQLSTATE,
      v_message  = MESSAGE_TEXT,
      v_context  = PG_EXCEPTION_CONTEXT;

    -- Write audit log via dblink — this commits independently
    -- even though our transaction rolled back to the savepoint
    PERFORM io.thecodeforge.log_error(
      'postgres_pattern',
      'account_id=' || p_account_id,
      v_sqlstate, v_message, NULL, v_context, NULL
    );

    -- Re-raise the original exception
    RAISE;
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- ORACLE AUTONOMOUS TRANSACTION PATTERN
-- (writing audit logs that survive rollback)
-- ============================================================

-- Oracle:
CREATE OR REPLACE PROCEDURE oracle_write_audit(
  p_event VARCHAR2,
  p_detail VARCHAR2
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log (event, detail, logged_at)
  VALUES (p_event, p_detail, SYSDATE);
  COMMIT;  -- This commit does NOT affect the outer transaction
END;
/

-- PostgreSQL equivalent via dblink:
CREATE OR REPLACE FUNCTION io.thecodeforge.write_audit(
  p_event  TEXT,
  p_detail TEXT
) RETURNS VOID AS $$
BEGIN
  -- dblink executes in a SEPARATE connection — committed immediately
  -- regardless of whether the calling transaction commits or rolls back
  PERFORM dblink_exec(
    'dbname=' || current_database() || ' user=' || SESSION_USER,
    format(
      'INSERT INTO io.thecodeforge.audit_log (event, detail, logged_at) '
      'VALUES (%L, %L, NOW())',
      p_event, p_detail
    )
  );
EXCEPTION WHEN OTHERS THEN
  -- Audit failure must never mask the original operation failure
  -- Fall back to server log if dblink write fails
  RAISE LOG 'Audit write failed: event=%, detail=%, error=%',
    p_event, p_detail, SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- PERFORMANCE: Comparing transaction patterns
-- ============================================================
-- Benchmark query — run for baseline before migration
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH timing AS (
  SELECT
    now() AS start_time,
    count(*) AS source_rows
  FROM staging_orders
  WHERE processed = FALSE
)
SELECT
  source_rows,
  now() - start_time AS planning_time
FROM timing;

-- ============================================================
-- ORACLE BULK COLLECT + FORALL PATTERN
-- ============================================================
-- Oracle:
CREATE OR REPLACE PROCEDURE oracle_bulk_process AS
  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids;
BEGIN
  SELECT order_id BULK COLLECT INTO v_ids
  FROM staging_orders
  WHERE processed = 'N';

  FORALL i IN 1 .. v_ids.COUNT SAVE EXCEPTIONS
    UPDATE orders
    SET status = 'PROCESSED'
    WHERE order_id = v_ids(i);

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -24381 THEN  -- bulk errors
      FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
          'Row ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
          ' failed: ORA-' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE
        );
      END LOOP;
    END IF;
END;
/

-- ============================================================
-- POSTGRESQL EQUIVALENT: Set-based + row-level error handling
-- ============================================================
CREATE OR REPLACE FUNCTION io.thecodeforge.postgres_bulk_process()
RETURNS TABLE(processed_count INTEGER, failed_count INTEGER) AS $$
DECLARE
  v_order     RECORD;
  v_processed INTEGER := 0;
  v_failed    INTEGER := 0;
  v_sqlstate  TEXT;
  v_message   TEXT;
  v_context   TEXT;
BEGIN
  -- Option A: Pure set-based (fastest, no per-row error handling)
  UPDATE orders o
  SET status = 'PROCESSED',
      processed_at = NOW()
  FROM staging_orders s
  WHERE o.order_id = s.order_id
    AND s.processed = FALSE;

  GET DIAGNOSTICS v_processed = ROW_COUNT;

  RETURN QUERY SELECT v_processed, 0;

  -- Option B: Row-level loop with per-row error handling
  -- (equivalent to Oracle's FORALL with SAVE EXCEPTIONS)
  -- Uncomment this block if you need to continue on individual row failures
  /*
  FOR v_order IN
    SELECT order_id FROM staging_orders WHERE processed = FALSE
  LOOP
    BEGIN
      UPDATE orders
      SET status = 'PROCESSED', processed_at = NOW()
      WHERE order_id = v_order.order_id;

      v_processed := v_processed + 1;
    EXCEPTION WHEN OTHERS THEN
      GET STACKED DIAGNOSTICS
        v_sqlstate = RETURNED_SQLSTATE,
        v_message  = MESSAGE_TEXT,
        v_context  = PG_EXCEPTION_CONTEXT;

      PERFORM io.thecodeforge.log_error(
        'postgres_bulk_process',
        'order_id=' || v_order.order_id,
        v_sqlstate, v_message, NULL, v_context, NULL
      );

      v_failed := v_failed + 1;
      -- CONTINUE to next row (no RAISE)
    END;
  END LOOP;

  RETURN QUERY SELECT v_processed, v_failed;
  */
END;
$$ LANGUAGE plpgsql;

-- ============================================================
-- IMPLICIT CURSOR ATTRIBUTE MIGRATION
-- ============================================================

-- Oracle SQL%ROWCOUNT -> PostgreSQL GET DIAGNOSTICS
-- Oracle:
BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 42;
  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Account not found');
  END IF;
END;
/

-- PostgreSQL:
DO $$
DECLARE
  v_count INTEGER;
BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 42;
  GET DIAGNOSTICS v_count = ROW_COUNT;

  IF v_count = 0 THEN
    RAISE EXCEPTION 'Account not found: account_id=42'
      USING ERRCODE = 'P0002';
  END IF;
END;
$$;

-- ============================================================
-- REFCURSOR MIGRATION
-- ============================================================

-- Oracle:
CREATE OR REPLACE PROCEDURE get_orders(
  p_status IN VARCHAR2,
  p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_cursor FOR
    SELECT order_id, customer_id, amount
    FROM orders
    WHERE status = p_status;
END;
/

-- PostgreSQL:
CREATE OR REPLACE FUNCTION io.thecodeforge.get_orders(
  p_status TEXT
) RETURNS refcursor AS $$
DECLARE
  v_cursor refcursor;
BEGIN
  OPEN v_cursor FOR
    SELECT order_id, customer_id, amount
    FROM orders
    WHERE status = p_status;
  RETURN v_cursor;
END;
$$ LANGUAGE plpgsql;