Oracle 19c vs 21c vs 23ai: Changes in Error Handling & PL/SQL

-- Oracle 19c: implicit conversion succeeds silently
-- This is the behavior that breaks on 23ai
DECLARE
  v_input  VARCHAR2(20) := '123.45  ';  -- trailing spaces from file parser
  v_number NUMBER;
BEGIN
  v_number := TO_NUMBER(v_input);  -- succeeds on 19c, FAILS on 23ai
  DBMS_OUTPUT.PUT_LINE('Converted: ' || v_number);
EXCEPTION
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Value error: ' || SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected: ' || SQLERRM);
END;
/
-- Output on 19c: Converted: 123.45
-- Output on 23ai: Value error: ORA-06502: PL/SQL: numeric or value error


-- Oracle 19c: ORA-06512 stack trace format (unchanged across all versions)
-- ORA-01403: no data found
-- ORA-06512: at "PAYMENTS.CURRENCY_CONVERT", line 42
-- ORA-06512: at "PAYMENTS.BATCH_PROCESSOR", line 187
-- ORA-06512: at line 1


-- Oracle 19c: FORMAT_ERROR_BACKTRACE behavior
CREATE OR REPLACE PROCEDURE inner_proc_19c IS
BEGIN
  RAISE NO_DATA_FOUND;
END inner_proc_19c;
/

CREATE OR REPLACE PROCEDURE middle_proc_19c IS
BEGIN
  inner_proc_19c;
END middle_proc_19c;
/

CREATE OR REPLACE PROCEDURE outer_proc_19c IS
BEGIN
  middle_proc_19c;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('--- BACKTRACE ---');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    DBMS_OUTPUT.PUT_LINE('--- ERROR STACK ---');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END outer_proc_19c;
/

BEGIN outer_proc_19c; END;
/
-- Output on 19c (consistent line numbers, full depth):
-- --- BACKTRACE ---
-- ORA-06512: at "MYSCHEMA.INNER_PROC_19C", line 3
-- ORA-06512: at "MYSCHEMA.MIDDLE_PROC_19C", line 3
-- ORA-06512: at "MYSCHEMA.OUTER_PROC_19C", line 3
-- --- ERROR STACK ---
-- ORA-01403: no data found


-- 19c: Qualified expression reduces ORA-06531 risk
DECLARE
  TYPE t_amounts IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  -- Pre-19c you had to declare then populate separately,
  -- risking ORA-06531 if you forgot to initialize
  v_amounts t_amounts := t_amounts(1 => 100.50, 2 => 200.75, 3 => 350.00);
BEGIN
  FOR i IN 1..v_amounts.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Amount ' || i || ': ' || v_amounts(i));
  END LOOP;
END;
/

-- Oracle 21c: Immutable table introduces new error code ORA-43923
CREATE IMMUTABLE TABLE audit_immutable_21c (
  event_id   NUMBER GENERATED ALWAYS AS IDENTITY,
  event_type VARCHAR2(50),
  event_data CLOB,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
) NO DROP UNTIL 365 DAYS IDLE
  NO DELETE UNTIL 730 DAYS AFTER INSERT;

-- INSERT works normally
INSERT INTO audit_immutable_21c (event_type, event_data)
VALUES ('LOGIN', '{"user": "admin", "ip": "10.0.1.15"}');
COMMIT;

-- UPDATE raises new error not present in 19c
BEGIN
  UPDATE audit_immutable_21c
  SET event_type = 'MODIFIED'
  WHERE event_id = 1;
EXCEPTION
  WHEN OTHERS THEN
    -- SQLCODE = -43923
    -- ORA-43923: Modification of the data in Immutable table is not allowed
    -- ORA-06512: at line 2
    DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Stack:   ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

-- Add PRAGMA EXCEPTION_INIT for the new error code
DECLARE
  e_immutable_violation EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_immutable_violation, -43923);
BEGIN
  DELETE FROM audit_immutable_21c WHERE event_id = 1;
EXCEPTION
  WHEN e_immutable_violation THEN
    DBMS_OUTPUT.PUT_LINE('Cannot modify immutable table - expected behavior');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected: ' || SQLERRM);
    RAISE;
END;
/


-- Oracle 21c: SQL Macro affects ORA-06512 line numbers
CREATE OR REPLACE FUNCTION active_orders_macro
  RETURN VARCHAR2 SQL_MACRO(TABLE) IS
BEGIN
  RETURN q'[
    SELECT o.order_id, o.customer_id, o.amount, o.status
    FROM   orders o
    WHERE  o.status = 'ACTIVE'
    AND    o.created_at > SYSDATE - 30
  ]';
END active_orders_macro;
/

-- When this macro is called and an error occurs inside it,
-- the ORA-06512 line number refers to the EXPANDED code
-- in the calling context, not the macro definition
CREATE OR REPLACE PROCEDURE process_active_orders_21c IS
  v_total NUMBER;
BEGIN
  SELECT SUM(amount) INTO v_total
  FROM   active_orders_macro();  -- macro expands here
  -- If the expansion causes an error, ORA-06512 reports
  -- a line number in THIS procedure, not in the macro
  DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No active orders found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error at expanded macro location');
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END process_active_orders_21c;
/


-- Oracle 21c: Native JSON data type
-- Error messages include additional binary format context
DECLARE
  v_json  JSON := JSON('{"amount": "not_a_number"}');
  v_amount NUMBER;
BEGIN
  v_amount := JSON_VALUE(v_json, '$.amount' RETURNING NUMBER);
  -- Default behavior without error clause: returns NULL
  -- With ERROR ON ERROR: raises ORA-40441 with enhanced diagnostics
  DBMS_OUTPUT.PUT_LINE('Amount: ' || NVL(TO_CHAR(v_amount), 'NULL'));
END;
/

-- Oracle 23ai: Stricter implicit conversion — THE most common upgrade breaker
DECLARE
  v_input  VARCHAR2(20) := '123.45  ';  -- trailing spaces from file parser
  v_number NUMBER;
BEGIN
  v_number := TO_NUMBER(v_input);  -- FAILS on 23ai with ORA-06502
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_number);
EXCEPTION
  WHEN VALUE_ERROR THEN
    -- This handler fires on 23ai but NEVER fires on 19c for this input
    DBMS_OUTPUT.PUT_LINE('23ai rejected input: [' || v_input || ']');
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Stack: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/

-- 23ai-safe fix: works on ALL versions (19c, 21c, 23ai)
DECLARE
  v_input  VARCHAR2(20) := '123.45  ';
  v_number NUMBER;
BEGIN
  v_number := TO_NUMBER(TRIM(v_input), '999999999999.99');
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_number);  -- 123.45 on all versions
END;
/


-- Oracle 23ai: Enhanced JSON error reporting with path and position detail
-- Compare error detail between versions
BEGIN
  DECLARE
    v_result NUMBER;
  BEGIN
    SELECT JSON_VALUE(
      '{"order": {"amount": "not_a_number", "currency": "USD"}}',
      '$.order.amount'
      RETURNING NUMBER
      ERROR ON ERROR
    ) INTO v_result FROM dual;
  EXCEPTION
    WHEN OTHERS THEN
      -- 19c: ORA-40441: JSON syntax error
      -- 23ai: ORA-40441: JSON syntax error at position 25
      --       for JSON path '$.order.amount'
      --       expected NUMBER, found STRING
      DBMS_OUTPUT.PUT_LINE('JSON Error: ' || SQLERRM);
      DBMS_OUTPUT.PUT_LINE('Stack: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  END;
END;
/


-- Oracle 23ai: BOOLEAN in SQL — changes error behavior in conditional paths
CREATE OR REPLACE FUNCTION is_premium_customer(
  p_customer_id IN NUMBER
) RETURN BOOLEAN IS
  v_total NUMBER;
BEGIN
  SELECT SUM(amount) INTO v_total
  FROM   orders
  WHERE  customer_id = p_customer_id
  AND    status = 'COMPLETED';
  
  RETURN NVL(v_total, 0) > 10000;
END is_premium_customer;
/

-- 23ai: BOOLEAN function directly in SQL WHERE clause
-- This was a syntax error on 19c and 21c
SELECT customer_id, name
FROM   customers
WHERE  is_premium_customer(customer_id);  -- 23ai only
-- If the function raises an exception, the error path now
-- includes SQL-context boolean evaluation, which produces
-- different FORMAT_ERROR_BACKTRACE output than PL/SQL-context


-- Oracle 23ai: SQL Domains introduce new constraint error codes
CREATE DOMAIN positive_amount AS NUMBER(12,2)
  CONSTRAINT positive_amount_chk CHECK (positive_amount > 0)
  DISPLAY CASE WHEN positive_amount > 1000 THEN TO_CHAR(positive_amount, '999,999.99')
              ELSE TO_CHAR(positive_amount, '999.99') END
  ORDER positive_amount;

CREATE TABLE domain_orders_23ai (
  order_id NUMBER GENERATED ALWAYS AS IDENTITY,
  amount   positive_amount  -- domain-typed column
);

-- Domain violation raises a DIFFERENT error code than CHECK constraint
BEGIN
  INSERT INTO domain_orders_23ai (amount) VALUES (-50.00);
EXCEPTION
  WHEN OTHERS THEN
    -- Domain violation error code differs from standard ORA-02290
    -- ORA-11534: check constraint violated for domain POSITIVE_AMOUNT
    DBMS_OUTPUT.PUT_LINE('Domain error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/


-- Oracle 23ai: IF NOT EXISTS eliminates entire error categories
-- No more ORA-00955 for CREATE or ORA-00942 for DROP
CREATE TABLE IF NOT EXISTS staging_temp (
  id   NUMBER,
  data CLOB
);
-- No error if table already exists — 19c would raise ORA-00955

DROP TABLE IF EXISTS staging_temp;
-- No error if table does not exist — 19c would raise ORA-00942


-- Oracle 23ai: SQL Firewall configuration for error-raising procedures
-- Run in observation mode first to capture baseline
BEGIN
  -- Enable SQL Firewall in observation mode
  DBMS_SQL_FIREWALL.ENABLE;
  
  -- Start capturing baseline for application user
  DBMS_SQL_FIREWALL.CREATE_CAPTURE(
    username      => 'APP_USER',
    top_level_only => FALSE,  -- capture nested calls too
    exclude_users  => SYS.DBMS_SQL_FIREWALL.USER_LIST('SYS','SYSTEM')
  );
  DBMS_SQL_FIREWALL.START_CAPTURE(username => 'APP_USER');
  
  -- After a full business cycle, stop capture and generate allow list
  -- DBMS_SQL_FIREWALL.STOP_CAPTURE(username => 'APP_USER');
  -- DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST(username => 'APP_USER');
  -- DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(username => 'APP_USER');
END;
/

-- Check for firewall violations that might be false positives
-- from legitimate error-raising procedures
SELECT username, sql_text, firewall_action, occurred_at
FROM   DBA_SQL_FIREWALL_VIOLATIONS
WHERE  username = 'APP_USER'
AND    UPPER(sql_text) LIKE '%RAISE_APPLICATION_ERROR%'
ORDER BY occurred_at DESC;

-- Version-agnostic ORA-06512 stack parsing function
-- Works identically on 19c, 21c, and 23ai
CREATE OR REPLACE FUNCTION parse_error_stack(
  p_error_stack IN CLOB
) RETURN SYS_REFCURSOR IS
  v_cursor SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR
    WITH stack_lines AS (
      SELECT
        REGEXP_SUBSTR(
          p_error_stack,
          'ORA-06512:[^' || CHR(10) || ']+',
          1, LEVEL
        ) AS line_text,
        LEVEL AS stack_position
      FROM dual
      CONNECT BY REGEXP_SUBSTR(
        p_error_stack,
        'ORA-06512:[^' || CHR(10) || ']+',
        1, LEVEL
      ) IS NOT NULL
    )
    SELECT
      stack_position,
      REGEXP_SUBSTR(line_text, 'at "([^"]+)"', 1, 1, NULL, 1) AS object_name,
      TO_NUMBER(
        REGEXP_SUBSTR(line_text, 'line (\d+)', 1, 1, NULL, 1)
      ) AS line_number,
      line_text AS raw_text
    FROM stack_lines
    ORDER BY stack_position;
  
  RETURN v_cursor;
END parse_error_stack;
/


-- Demonstration: parse a multi-frame stack trace
DECLARE
  v_stack CLOB :=
    'ORA-01403: no data found' || CHR(10) ||
    'ORA-06512: at "PAYMENTS.CURRENCY_CONVERT", line 42' || CHR(10) ||
    'ORA-06512: at "PAYMENTS.BATCH_PROCESSOR", line 187' || CHR(10) ||
    'ORA-06512: at "PAYMENTS.NIGHTLY_JOB", line 15' || CHR(10) ||
    'ORA-06512: at line 1';
  v_cursor  SYS_REFCURSOR;
  v_pos     NUMBER;
  v_object  VARCHAR2(261);
  v_line    NUMBER;
  v_raw     VARCHAR2(4000);
BEGIN
  v_cursor := parse_error_stack(v_stack);
  DBMS_OUTPUT.PUT_LINE('Pos  Object Name                      Line');
  DBMS_OUTPUT.PUT_LINE('---  -------------------------------  ----');
  LOOP
    FETCH v_cursor INTO v_pos, v_object, v_line, v_raw;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(
      LPAD(v_pos, 3) || '  ' ||
      RPAD(NVL(v_object, '<anonymous>'), 33) ||
      NVL(TO_CHAR(v_line), 'N/A')
    );
  END LOOP;
  CLOSE v_cursor;
END;
/
-- Output (identical on 19c, 21c, 23ai):
-- Pos  Object Name                      Line
-- ---  -------------------------------  ----
--   1  PAYMENTS.CURRENCY_CONVERT        42
--   2  PAYMENTS.BATCH_PROCESSOR         187
--   3  PAYMENTS.NIGHTLY_JOB             15
--   4  <anonymous>                      1


-- Version detection for conditional error handling
-- Useful when the same codebase must run on multiple versions
CREATE OR REPLACE FUNCTION get_oracle_major_version
  RETURN NUMBER IS
  v_version VARCHAR2(50);
BEGIN
  SELECT version_full INTO v_version FROM v$instance;
  RETURN TO_NUMBER(REGEXP_SUBSTR(v_version, '^\d+'));
END get_oracle_major_version;
/

-- Example: version-aware error handler
CREATE OR REPLACE PROCEDURE version_aware_handler IS
  v_major NUMBER := get_oracle_major_version;
BEGIN
  -- business logic here
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    IF v_major >= 23 THEN
      -- 23ai provides richer diagnostics — log them
      DBMS_OUTPUT.PUT_LINE('23ai stack: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    ELSE
      -- 19c/21c standard handling
      DBMS_OUTPUT.PUT_LINE('Stack: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    END IF;
    RAISE;
END version_aware_handler;
/

-- 19c: Qualified expressions reduce ORA-06531 risk
-- Before 19c, this pattern was common and error-prone:
DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids;  -- uninitialized!
BEGIN
  v_ids.EXTEND;  -- ORA-06531: Reference to uninitialized collection
  v_ids(1) := 42;
EXCEPTION
  WHEN COLLECTION_IS_NULL THEN
    DBMS_OUTPUT.PUT_LINE('Forgot to initialize: ' || SQLERRM);
END;
/

-- 19c qualified expression eliminates the error entirely:
DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids := t_ids(42, 99, 157);  -- initialized inline
BEGIN
  FOR i IN 1..v_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || v_ids(i));
  END LOOP;
  -- COLLECTION_IS_NULL handler is now dead code
END;
/


-- 21c: JSON Duality View errors include JSON path diagnostics
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW order_dv AS
  orders @INSERT @UPDATE @DELETE {
    _id        : order_id,
    customerId : customer_id,
    orderDate  : order_date,
    amount     : amount,
    items      : order_items @INSERT @UPDATE @DELETE [{
      itemId    : item_id,
      productId : product_id,
      quantity  : quantity,
      unitPrice : unit_price
    }]
  };
/

-- DML through duality view produces errors with JSON path context
BEGIN
  INSERT INTO order_dv
  VALUES ('{"_id": 1, "customerId": null, "amount": 100}');
  -- If customer_id has a NOT NULL constraint:
  -- ORA-02290: check constraint (SCHEMA.SYS_C00XXXX) violated
  -- with additional JSON context: at path $.customerId
EXCEPTION
  WHEN OTHERS THEN
    -- SQLERRM includes JSON path information on 21c/23ai
    -- This format does not exist on 19c
    DBMS_OUTPUT.PUT_LINE('Duality error: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Stack: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
/


-- 23ai: IF NOT EXISTS eliminates entire error categories
-- Before 23ai, you needed this:
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE staging_temp (id NUMBER, data CLOB)';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -955 THEN
      NULL;  -- table already exists, ignore
    ELSE
      RAISE;
    END IF;
END;
/

-- 23ai: no exception handler needed
CREATE TABLE IF NOT EXISTS staging_temp (id NUMBER, data CLOB);
-- ORA-00955 is impossible with IF NOT EXISTS
-- The exception handler above is now dead code on 23ai


-- 23ai: SQL Domain constraint errors — new error code
CREATE DOMAIN email_address AS VARCHAR2(320)
  CONSTRAINT email_format_chk
    CHECK (REGEXP_LIKE(email_address, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'));

CREATE TABLE contacts_23ai (
  contact_id NUMBER GENERATED ALWAYS AS IDENTITY,
  email      email_address
);

DECLARE
  e_domain_violation EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_domain_violation, -11534);
BEGIN
  INSERT INTO contacts_23ai (email) VALUES ('not-an-email');
EXCEPTION
  WHEN e_domain_violation THEN
    -- ORA-11534: domain constraint violated — NOT the same as ORA-02290
    DBMS_OUTPUT.PUT_LINE('Domain violation: ' || SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Other error: ' || SQLCODE || ' - ' || SQLERRM);
    RAISE;
END;
/


-- Version-aware error handling package skeleton
CREATE OR REPLACE PACKAGE error_handler_pkg IS
  -- Standard errors (all versions)
  e_no_data         EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_no_data, -1403);
  
  e_value_error     EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_value_error, -6502);
  
  e_dup_val         EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_dup_val, -1);
  
  -- 21c+ errors
  e_immutable_dml   EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_immutable_dml, -43923);
  
  -- 23ai+ errors
  e_domain_violation EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_domain_violation, -11534);
  
  PROCEDURE handle_and_log(
    p_context IN VARCHAR2,
    p_reraise IN BOOLEAN DEFAULT TRUE
  );
END error_handler_pkg;
/

CREATE OR REPLACE PACKAGE BODY error_handler_pkg IS
  PROCEDURE handle_and_log(
    p_context IN VARCHAR2,
    p_reraise IN BOOLEAN DEFAULT TRUE
  ) IS
  BEGIN
    -- Log with full diagnostics available on the running version
    INSERT INTO error_log (error_timestamp, context, error_code,
                           error_message, error_backtrace)
    VALUES (SYSTIMESTAMP, p_context, SQLCODE,
            SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    COMMIT;
    
    IF p_reraise THEN
      RAISE;
    END IF;
  END handle_and_log;
END error_handler_pkg;
/

-- Phase 1: Inventory — find all error handling code
-- Run on source version to build the test map

-- Find all exception handlers
SELECT owner, name, type, line, TRIM(text) AS text
FROM   dba_source
WHERE  UPPER(text) LIKE '%EXCEPTION%'
AND    UPPER(text) NOT LIKE '%--%EXCEPTION%'  -- exclude comments
AND    owner NOT IN ('SYS','SYSTEM','XDB','MDSYS','CTXSYS','WMSYS')
ORDER BY owner, name, line;

-- Find all WHEN OTHERS handlers (highest risk for silent swallowing)
SELECT owner, name, type, line, TRIM(text) AS text
FROM   dba_source
WHERE  UPPER(text) LIKE '%WHEN OTHERS%'
AND    owner NOT IN ('SYS','SYSTEM','XDB','MDSYS','CTXSYS','WMSYS')
ORDER BY owner, name, line;

-- Find all implicit conversions (highest risk for 23ai breakage)
SELECT owner, name, type, line, TRIM(text) AS text
FROM   dba_source
WHERE  REGEXP_LIKE(text, 'TO_NUMBER\s*\([^,)'']+\)', 'i')  -- no format mask
AND    owner NOT IN ('SYS','SYSTEM','XDB','MDSYS','CTXSYS','WMSYS')
ORDER BY owner, name, line;


-- Phase 2 & 3: Test harness — captures error behavior for comparison
CREATE TABLE error_test_results (
  test_id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  test_name      VARCHAR2(200)   NOT NULL,
  oracle_version VARCHAR2(50)    NOT NULL,
  test_input     VARCHAR2(4000),
  expected_error VARCHAR2(20),
  actual_sqlcode NUMBER,
  actual_sqlerrm VARCHAR2(4000),
  actual_stack   CLOB,
  pass_fail      VARCHAR2(4)     NOT NULL,
  executed_at    TIMESTAMP       DEFAULT SYSTIMESTAMP
);

CREATE OR REPLACE PROCEDURE run_upgrade_error_tests IS
  v_version VARCHAR2(50);
  
  PROCEDURE log_result(
    p_test_name  IN VARCHAR2,
    p_input      IN VARCHAR2,
    p_expected   IN VARCHAR2,
    p_sqlcode    IN NUMBER,
    p_sqlerrm    IN VARCHAR2,
    p_stack      IN CLOB
  ) IS
    v_status VARCHAR2(4);
  BEGIN
    IF (p_expected = 'SUCCESS' AND p_sqlcode = 0)
    OR (p_expected = TO_CHAR(p_sqlcode)) THEN
      v_status := 'PASS';
    ELSE
      v_status := 'FAIL';
    END IF;
    
    INSERT INTO error_test_results (
      test_name, oracle_version, test_input,
      expected_error, actual_sqlcode, actual_sqlerrm,
      actual_stack, pass_fail
    ) VALUES (
      p_test_name, v_version, p_input,
      p_expected, p_sqlcode, p_sqlerrm,
      p_stack, v_status
    );
  END log_result;
  
BEGIN
  SELECT version_full INTO v_version FROM v$instance;
  
  -- Test 1: Implicit conversion with trailing whitespace
  DECLARE
    v_result NUMBER;
    v_code   NUMBER := 0;
    v_msg    VARCHAR2(4000) := 'SUCCESS';
    v_stack  CLOB := NULL;
  BEGIN
    v_result := TO_NUMBER('123.45  ');
  EXCEPTION
    WHEN OTHERS THEN
      v_code  := SQLCODE;
      v_msg   := SQLERRM;
      v_stack := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  END;
  log_result('IMPLICIT_CONV_TRAILING_SPACE', '123.45  ', 'SUCCESS',
             v_code, v_msg, v_stack);
  
  -- Test 2: Implicit conversion with leading whitespace
  DECLARE
    v_result NUMBER;
    v_code   NUMBER := 0;
    v_msg    VARCHAR2(4000) := 'SUCCESS';
    v_stack  CLOB := NULL;
  BEGIN
    v_result := TO_NUMBER('  123.45');
  EXCEPTION
    WHEN OTHERS THEN
      v_code  := SQLCODE;
      v_msg   := SQLERRM;
      v_stack := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  END;
  log_result('IMPLICIT_CONV_LEADING_SPACE', '  123.45', 'SUCCESS',
             v_code, v_msg, v_stack);
  
  -- Test 3: Implicit conversion with empty string
  DECLARE
    v_result NUMBER;
    v_code   NUMBER := 0;
    v_msg    VARCHAR2(4000) := 'SUCCESS';
    v_stack  CLOB := NULL;
  BEGIN
    v_result := TO_NUMBER('');
  EXCEPTION
    WHEN OTHERS THEN
      v_code  := SQLCODE;
      v_msg   := SQLERRM;
      v_stack := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
  END;
  log_result('IMPLICIT_CONV_EMPTY_STRING', '', 'SUCCESS',
             v_code, v_msg, v_stack);
  
  -- Test 4: FORMAT_ERROR_BACKTRACE depth
  DECLARE
    v_stack CLOB;
    v_depth NUMBER;
  BEGIN
    BEGIN
      RAISE NO_DATA_FOUND;
    EXCEPTION
      WHEN OTHERS THEN
        v_stack := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
        SELECT COUNT(*) INTO v_depth
        FROM dual
        CONNECT BY REGEXP_SUBSTR(v_stack, 'ORA-06512', 1, LEVEL) IS NOT NULL;
    END;
    log_result('BACKTRACE_DEPTH', NULL, NULL,
               v_depth, 'Depth: ' || v_depth, v_stack);
  END;
  
  COMMIT;
END run_upgrade_error_tests;
/

-- Execute on source version
BEGIN run_upgrade_error_tests; END;
/

-- Execute on target version (same procedure, same test data)
BEGIN run_upgrade_error_tests; END;
/


-- Phase 4: Cross-version comparison report
-- Run this after executing tests on both versions
SELECT
  t1.test_name,
  t1.oracle_version   AS source_version,
  t1.actual_sqlcode    AS source_sqlcode,
  SUBSTR(t1.actual_sqlerrm, 1, 80) AS source_error,
  t2.oracle_version    AS target_version,
  t2.actual_sqlcode    AS target_sqlcode,
  SUBSTR(t2.actual_sqlerrm, 1, 80) AS target_error,
  CASE
    WHEN t1.actual_sqlcode = t2.actual_sqlcode THEN 'CONSISTENT'
    ELSE '*** BEHAVIOR CHANGED ***'
  END AS comparison_status
FROM   error_test_results t1
JOIN   error_test_results t2
  ON   t1.test_name = t2.test_name
  AND  t1.oracle_version != t2.oracle_version
WHERE  t1.oracle_version < t2.oracle_version  -- source before target
ORDER BY
  CASE WHEN t1.actual_sqlcode != t2.actual_sqlcode THEN 0 ELSE 1 END,
  t1.test_name;