ORA-06550 — A Missing Semicolon Took 47 Objects Offline

-- ============================================================
-- Setup: create a table and helper package for the examples
-- Runnable on Oracle 19c, 21c, 23ai
-- ============================================================

CREATE TABLE demo_accounts (
  account_id   NUMBER PRIMARY KEY,
  account_type VARCHAR2(30),
  balance      NUMBER(15,2)
);

INSERT INTO demo_accounts VALUES (1, 'CHECKING', 5000);
COMMIT;

CREATE OR REPLACE PACKAGE demo_util_pkg AS
  FUNCTION calculate_tax(p_amount IN NUMBER, p_region IN VARCHAR2) RETURN NUMBER;
END demo_util_pkg;
/

CREATE OR REPLACE PACKAGE BODY demo_util_pkg AS
  FUNCTION calculate_tax(p_amount IN NUMBER, p_region IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN p_amount * 0.08;
  END calculate_tax;
END demo_util_pkg;
/

-- ============================================================
-- Example 1: PLS-00103 — Missing semicolon after variable declaration
-- ============================================================

CREATE OR REPLACE PROCEDURE demo_syntax_error IS
  v_count NUMBER   -- Missing semicolon here
BEGIN
  SELECT COUNT(*) INTO v_count FROM demo_accounts;
END demo_syntax_error;
/

-- Error output:
-- ORA-06550: line 3, column 1:
-- PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
--    := . ( @ % ; not null range default character

-- Fix: add semicolon after NUMBER on line 2

SHOW ERRORS PROCEDURE DEMO_SYNTAX_ERROR

-- ============================================================
-- Example 2: PLS-00201 — Undeclared variable
-- ============================================================

CREATE OR REPLACE PROCEDURE demo_undeclared_var IS
BEGIN
  v_total := 100;   -- v_total was never declared
  DBMS_OUTPUT.PUT_LINE(v_total);
END demo_undeclared_var;
/

-- Error output:
-- ORA-06550: line 3, column 3:
-- PLS-00201: identifier 'V_TOTAL' must be declared

-- Fix: add DECLARE section with v_total NUMBER;

-- ============================================================
-- Example 3: PLS-00306 — Wrong number of arguments
-- ============================================================

CREATE OR REPLACE PROCEDURE demo_wrong_args IS
  v_result NUMBER;
BEGIN
  -- calculate_tax expects 2 args (amount, region) — we passed 3
  v_result := demo_util_pkg.calculate_tax(100, 'CA', 'EXTRA');
END demo_wrong_args;
/

-- Error output:
-- ORA-06550: line 5, column 16:
-- PLS-00306: wrong number or types of arguments in call to 'CALCULATE_TAX'

-- Fix: remove the extra argument

-- ============================================================
-- Example 4: PLS-00302 — Component not declared on record type
-- ============================================================

CREATE OR REPLACE PROCEDURE demo_missing_component IS
  v_rec demo_accounts%ROWTYPE;
BEGIN
  SELECT * INTO v_rec FROM demo_accounts WHERE account_id = 1;
  DBMS_OUTPUT.PUT_LINE(v_rec.nonexistent_field);  -- Field does not exist
END demo_missing_component;
/

-- Error output:
-- ORA-06550: line 5, column 32:
-- PLS-00302: component 'NONEXISTENT_FIELD' must be declared

-- Fix: use a valid column name from demo_accounts (e.g., v_rec.balance)

-- ============================================================
-- Retrieving all compilation errors for a specific object
-- ============================================================

SELECT
  name,
  type,
  line,
  position,
  text AS error_text
FROM user_errors
WHERE name = 'DEMO_SYNTAX_ERROR'
ORDER BY sequence;

-- Cleanup
-- DROP PROCEDURE demo_syntax_error;
-- DROP PROCEDURE demo_undeclared_var;
-- DROP PROCEDURE demo_wrong_args;
-- DROP PROCEDURE demo_missing_component;
-- DROP PACKAGE demo_util_pkg;
-- DROP TABLE demo_accounts PURGE;

-- ============================================================
-- SYNTAX ERRORS — the code violates PL/SQL grammar
-- Fix: correct the code structure
-- ============================================================

-- Missing semicolon — PLS-00103
CREATE OR REPLACE PROCEDURE demo_missing_semicolon IS
  v_count NUMBER   -- Semicolon missing here
BEGIN
  NULL;
END demo_missing_semicolon;
/
-- Fix: add ; after NUMBER

-- Unmatched parenthesis — PLS-00103
CREATE OR REPLACE PROCEDURE demo_unmatched_paren IS
  v_result NUMBER;
BEGIN
  v_result := demo_util_pkg.calculate_tax(100, 'CA';  -- Missing closing )
END demo_unmatched_paren;
/
-- Fix: add closing ) before ;

-- ============================================================
-- SEMANTIC ERRORS — code is grammatically correct but references
-- something that does not exist or cannot be accessed
-- Fix: may be code, database objects, or grants
-- ============================================================

-- Undeclared variable — PLS-00201 — fix is in the code
CREATE OR REPLACE PROCEDURE demo_undeclared IS
BEGIN
  v_total := 100;  -- v_total not declared
END demo_undeclared;
/
-- Fix: add v_total NUMBER; in the DECLARE section

-- Missing table — PLS-00201 — fix is a database object or synonym
CREATE OR REPLACE PROCEDURE demo_missing_table IS
  v_name VARCHAR2(100);
BEGIN
  SELECT name INTO v_name FROM nonexistent_table_xyz WHERE id = 1;
END demo_missing_table;
/
-- Fix: create the table, create a synonym to the correct table,
--   or correct the table name

-- Missing privilege — PLS-00904 — fix is a GRANT
CREATE OR REPLACE PROCEDURE demo_privilege_error IS
BEGIN
  other_schema.restricted_pkg.do_something;
END demo_privilege_error;
/
-- Fix: GRANT EXECUTE ON other_schema.restricted_pkg TO current_schema;
-- The code is correct — the privilege is missing

-- ============================================================
-- Diagnostic: determine if the referenced object exists
-- Run this when PLS-00201 or PLS-00904 appears
-- ============================================================

-- Check if the object exists anywhere in the database
SELECT owner, object_name, object_type, status
FROM all_objects
WHERE object_name = 'RESTRICTED_PKG'
ORDER BY owner;

-- Check if a synonym exists for the object
SELECT owner, synonym_name, table_owner, table_name
FROM all_synonyms
WHERE synonym_name = 'RESTRICTED_PKG';

-- Check direct grants on the object
SELECT grantor, grantee, privilege, grantable
FROM dba_tab_privs
WHERE table_name = 'RESTRICTED_PKG'
  AND grantee = 'CURRENT_SCHEMA'
ORDER BY privilege;

-- ============================================================
-- Demonstrate cascading invalidation
-- ============================================================

-- Base package — the root of the dependency tree
CREATE OR REPLACE PACKAGE base_pkg AS
  FUNCTION get_tax_rate(p_region IN VARCHAR2) RETURN NUMBER;
END base_pkg;
/

CREATE OR REPLACE PACKAGE BODY base_pkg AS
  FUNCTION get_tax_rate(p_region IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN 0.08;
  END get_tax_rate;
END base_pkg;
/

-- Dependent procedure — calls base_pkg
CREATE OR REPLACE PROCEDURE calc_order_total(
  p_amount IN NUMBER,
  p_region IN VARCHAR2,
  p_total  OUT NUMBER
) IS
  v_tax_rate NUMBER;
BEGIN
  v_tax_rate := base_pkg.get_tax_rate(p_region);
  p_total := p_amount + (p_amount * v_tax_rate);
END calc_order_total;
/

-- Dependent view — references a function from base_pkg
CREATE OR REPLACE VIEW order_tax_view AS
SELECT
  a.account_id,
  a.balance,
  base_pkg.get_tax_rate('US') AS tax_rate,
  a.balance * (1 + base_pkg.get_tax_rate('US')) AS total_with_tax
FROM demo_accounts a;

-- Verify: all objects are VALID
SELECT object_name, object_type, status
FROM user_objects
WHERE object_name IN ('BASE_PKG', 'CALC_ORDER_TOTAL', 'ORDER_TAX_VIEW')
ORDER BY object_type;
-- Expected: all status = VALID

-- ============================================================
-- Now break the base package — introduce a compilation error
-- ============================================================

CREATE OR REPLACE PACKAGE BODY base_pkg AS
  FUNCTION get_tax_rate(p_region IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN 0.08   -- Missing semicolon — ORA-06550
  END get_tax_rate;
END base_pkg;
/

-- Check: base_pkg is now INVALID
-- Check: dependents are also INVALID
SELECT object_name, object_type, status
FROM user_objects
WHERE object_name IN ('BASE_PKG', 'CALC_ORDER_TOTAL', 'ORDER_TAX_VIEW')
ORDER BY object_type;
-- Expected: all status = INVALID

-- Try to call the dependent procedure — ORA-04063
DECLARE
  v_total NUMBER;
BEGIN
  calc_order_total(100, 'US', v_total);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    -- ORA-04063: package body "DEMO.BASE_PKG" has errors
END;
/

-- ============================================================
-- Fix: correct the base package, recompile, revalidate
-- ============================================================

-- Step 1: Find the root cause
SELECT name, type, line, position, text
FROM user_errors
WHERE name = 'BASE_PKG'
ORDER BY sequence;

-- Step 2: Fix and recompile the base package
CREATE OR REPLACE PACKAGE BODY base_pkg AS
  FUNCTION get_tax_rate(p_region IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN 0.08;  -- Fixed: semicolon added
  END get_tax_rate;
END base_pkg;
/

-- Step 3: Verify base package is VALID
SELECT status FROM user_objects
WHERE object_name = 'BASE_PKG' AND object_type = 'PACKAGE BODY';

-- Step 4: Force revalidation of all INVALID objects
-- Option A: UTL_RECOMP (recommended for production)
EXEC UTL_RECOMP.RECOMP_SERIAL;

-- Option B: DBMS_UTILITY.COMPILE_SCHEMA (recompiles everything)
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => USER, compile_all => FALSE);
-- compile_all => FALSE: only recompiles INVALID objects

-- Step 5: Verify all objects are VALID
SELECT object_name, object_type, status
FROM user_objects
WHERE object_name IN ('BASE_PKG', 'CALC_ORDER_TOTAL', 'ORDER_TAX_VIEW')
ORDER BY object_type;
-- Expected: all status = VALID

-- ============================================================
-- View the dependency tree before deploying changes
-- Run this BEFORE deploying to estimate the blast radius
-- ============================================================

SELECT
  name         AS dependent_name,
  type         AS dependent_type,
  referenced_name,
  referenced_type
FROM user_dependencies
WHERE referenced_name = 'BASE_PKG'
ORDER BY type, name;

-- Count dependents by type
SELECT type, COUNT(*) AS dependent_count
FROM user_dependencies
WHERE referenced_name = 'BASE_PKG'
GROUP BY type
ORDER BY COUNT(*) DESC;

-- Cleanup
-- DROP VIEW order_tax_view;
-- DROP PROCEDURE calc_order_total;
-- DROP PACKAGE base_pkg;

-- ============================================================
-- UNSAFE: CREATE OR REPLACE with no validation
-- The script reports success even if the object is INVALID
-- ============================================================

CREATE OR REPLACE PROCEDURE unsafe_deploy_example IS
  v_count NUMBER   -- Missing semicolon
BEGIN
  NULL;
END unsafe_deploy_example;
/
-- SQL*Plus output: "Procedure created."
-- But the procedure is INVALID — no error is raised

-- ============================================================
-- SAFE Pattern 1: SHOW ERRORS after every CREATE OR REPLACE
-- For SQL*Plus and SQLcl deployment scripts
-- ============================================================

CREATE OR REPLACE PROCEDURE safe_deploy_example IS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM demo_accounts;
END safe_deploy_example;
/

SHOW ERRORS PROCEDURE SAFE_DEPLOY_EXAMPLE
-- If compilation errors exist, they are displayed here
-- Add WHENEVER SQLERROR EXIT to abort the script on errors

-- ============================================================
-- SAFE Pattern 2: Query USER_ERRORS after deployment
-- For programmatic deployment tools (Flyway, Liquibase, custom scripts)
-- ============================================================

DECLARE
  v_error_count NUMBER;
  v_error_text  VARCHAR2(4000);
BEGIN
  SELECT COUNT(*) INTO v_error_count
  FROM user_errors
  WHERE name = 'SAFE_DEPLOY_EXAMPLE'
    AND type = 'PROCEDURE';

  IF v_error_count > 0 THEN
    -- Get the first error for the exception message
    SELECT text INTO v_error_text
    FROM user_errors
    WHERE name = 'SAFE_DEPLOY_EXAMPLE'
      AND type = 'PROCEDURE'
    ORDER BY sequence
    FETCH FIRST 1 ROW ONLY;

    RAISE_APPLICATION_ERROR(
      -20060,
      'Deployment failed: SAFE_DEPLOY_EXAMPLE has ' || v_error_count
      || ' compilation error(s). First error: ' || v_error_text
    );
  END IF;

  DBMS_OUTPUT.PUT_LINE('SAFE_DEPLOY_EXAMPLE compiled successfully');
END;
/

-- ============================================================
-- SAFE Pattern 3: ALTER COMPILE — raises on failure
-- Use for validation after CREATE OR REPLACE
-- ============================================================

-- ALTER COMPILE raises an exception if compilation fails
-- Unlike CREATE OR REPLACE, it does NOT complete silently
ALTER PROCEDURE safe_deploy_example COMPILE;
-- If successful: no output, object is VALID
-- If failed: ORA-24344 or ORA-06550 is raised

-- For packages:
ALTER PACKAGE billing_pkg COMPILE;        -- Compiles the spec
ALTER PACKAGE billing_pkg COMPILE BODY;   -- Compiles the body

-- ============================================================
-- SAFE Pattern 4: Post-deployment health check
-- Run after the entire deployment to catch any INVALID objects
-- ============================================================

CREATE OR REPLACE PROCEDURE post_deployment_check IS
  v_invalid_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_invalid_count
  FROM user_objects
  WHERE status = 'INVALID';

  IF v_invalid_count > 0 THEN
    FOR rec IN (
      SELECT o.object_name, o.object_type,
             NVL((SELECT e.text FROM user_errors e WHERE e.name = o.object_name
                  ORDER BY e.sequence FETCH FIRST 1 ROW ONLY), 'no error details') AS first_error
      FROM user_objects o
      WHERE o.status = 'INVALID'
      ORDER BY o.object_type, o.object_name
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(
        'INVALID: ' || rec.object_type || ' ' || rec.object_name
        || ' — ' || rec.first_error
      );
    END LOOP;

    RAISE_APPLICATION_ERROR(
      -20061,
      'Post-deployment check failed: ' || v_invalid_count
      || ' object(s) are INVALID. Check DBMS_OUTPUT for details.'
    );
  END IF;

  DBMS_OUTPUT.PUT_LINE('Post-deployment check passed: all objects VALID');
END post_deployment_check;
/

-- Run after every deployment:
EXEC post_deployment_check;

-- ============================================================
-- PLS-00904 scenarios and fixes
-- ============================================================

-- Scenario 1: Missing EXECUTE grant on another schema's package
-- This procedure references FINANCE_SCHEMA.FINANCE_PKG

CREATE OR REPLACE PROCEDURE call_finance_validation IS
BEGIN
  finance_schema.finance_pkg.validate_account(12345);
  -- PLS-00904: insufficient privilege to access object FINANCE_PKG
END call_finance_validation;
/

-- Fix: direct grant to the compiling schema
-- Run as FINANCE_SCHEMA or a DBA:
GRANT EXECUTE ON finance_schema.finance_pkg TO billing_schema;

-- Then recompile:
ALTER PROCEDURE call_finance_validation COMPILE;

-- ============================================================
-- Scenario 2: Role-based grant does NOT work for definer's rights
-- ============================================================

-- This grant goes through a role:
GRANT EXECUTE ON finance_schema.finance_pkg TO app_role;
GRANT app_role TO billing_schema;
-- billing_schema has the privilege via the role
-- But definer's rights compilation STILL raises PLS-00904

-- The fix: use a direct grant, or switch to invoker's rights

-- ============================================================
-- Scenario 3: Invoker's rights — role-based grants work
-- ============================================================

CREATE OR REPLACE PROCEDURE call_finance_validation_ir
  AUTHID CURRENT_USER   -- Invoker's rights
IS
BEGIN
  finance_schema.finance_pkg.validate_account(12345);
  -- Resolves at execution time using the caller's privileges
  -- Role-based grants work because they are active during execution
END call_finance_validation_ir;
/

-- ============================================================
-- Diagnostic: find all PLS-00904 errors in the schema
-- ============================================================

SELECT
  name,
  type,
  line,
  text
FROM user_errors
WHERE text LIKE '%PLS-00904%'
ORDER BY name, sequence;

-- ============================================================
-- Diagnostic: check what direct grants exist for the compiling schema
-- ============================================================

SELECT
  grantor,
  privilege,
  table_name AS object_name,
  grantable
FROM dba_tab_privs
WHERE grantee = 'BILLING_SCHEMA'
  AND privilege = 'EXECUTE'
ORDER BY table_name;

-- Check role-based grants (will not help for definer's rights)
SELECT
  granted_role,
  admin_option
FROM dba_role_privs
WHERE grantee = 'BILLING_SCHEMA'
ORDER BY granted_role;

-- ============================================================
-- SQL*Plus deployment script with compilation validation
-- ============================================================

-- Abort script on any error
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

SET SERVEROUTPUT ON

PROMPT Deploying BILLING_PKG specification...

CREATE OR REPLACE PACKAGE billing_pkg AS
  PROCEDURE process_payment(p_account_id IN NUMBER, p_amount IN NUMBER);
  FUNCTION get_balance(p_account_id IN NUMBER) RETURN NUMBER;
END billing_pkg;
/

SHOW ERRORS PACKAGE BILLING_PKG

PROMPT Deploying BILLING_PKG body...

CREATE OR REPLACE PACKAGE BODY billing_pkg AS
  PROCEDURE process_payment(p_account_id IN NUMBER, p_amount IN NUMBER) IS
    v_balance NUMBER;
  BEGIN
    v_balance := get_balance(p_account_id);
    IF v_balance < p_amount THEN
      RAISE_APPLICATION_ERROR(-20001, 'Insufficient balance');
    END IF;
    UPDATE demo_accounts SET balance = balance - p_amount
    WHERE account_id = p_account_id;
  END process_payment;

  FUNCTION get_balance(p_account_id IN NUMBER) RETURN NUMBER IS
    v_balance NUMBER;
  BEGIN
    SELECT balance INTO v_balance FROM demo_accounts
    WHERE account_id = p_account_id;
    RETURN v_balance;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20002, 'Account not found: ' || p_account_id);
  END get_balance;
END billing_pkg;
/

SHOW ERRORS PACKAGE BODY BILLING_PKG

-- ============================================================
-- Post-deployment validation — run after all objects are deployed
-- ============================================================

PROMPT Running post-deployment validation...

DECLARE
  v_invalid_count NUMBER;
BEGIN
  -- Recompile any remaining INVALID objects
  UTL_RECOMP.RECOMP_SERIAL;

  -- Check for any remaining INVALID objects
  SELECT COUNT(*) INTO v_invalid_count
  FROM user_objects
  WHERE status = 'INVALID';

  IF v_invalid_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_invalid_count || ' INVALID objects remain:');
    FOR rec IN (
      SELECT object_name, object_type FROM user_objects
      WHERE status = 'INVALID'
      ORDER BY object_type, object_name
    ) LOOP
      DBMS_OUTPUT.PUT_LINE('  ' || rec.object_type || ' ' || rec.object_name);
    END LOOP;

    RAISE_APPLICATION_ERROR(
      -20062,
      'Post-deployment validation failed: ' || v_invalid_count || ' INVALID objects'
    );
  END IF;

  DBMS_OUTPUT.PUT_LINE('Post-deployment validation passed: all objects VALID');
END;
/

PROMPT Deployment complete.

-- ============================================================
-- CI/CD pipeline compilation check (conceptual — adapt to your pipeline)
-- Run in a staging schema before deploying to production
-- ============================================================

-- Step 1: Deploy all objects to the staging schema
-- (handled by your CI/CD tool — Flyway, Liquibase, custom scripts)

-- Step 2: Compile all objects and check for errors
BEGIN
  UTL_RECOMP.RECOMP_SERIAL;
END;
/

-- Step 3: Fail the pipeline if any objects are INVALID
DECLARE
  v_invalid_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_invalid_count
  FROM user_objects
  WHERE status = 'INVALID'
    AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER');

  IF v_invalid_count > 0 THEN
    -- Output errors for the CI log
    FOR rec IN (
      SELECT o.object_name, o.object_type, e.line, e.text AS error_text
      FROM user_objects o
      LEFT JOIN user_errors e ON e.name = o.object_name AND e.type = o.object_type
      WHERE o.status = 'INVALID'
      ORDER BY o.object_name, e.sequence
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(
        rec.object_type || ' ' || rec.object_name
        || CASE WHEN rec.line IS NOT NULL
           THEN ' Line ' || rec.line || ': ' || rec.error_text
           ELSE ' (no error details — dependency invalidation)'
           END
      );
    END LOOP;

    RAISE_APPLICATION_ERROR(-20063, 'CI compilation check failed');
  END IF;
END;
/

============================================================
JAVA / JDBC — ORA-06550 and ORA-04063 handling
============================================================

try {
    CallableStatement cs = conn.prepareCall(
        "BEGIN billing_pkg.process_payment(?, ?); END;"
    );
    cs.setInt(1, accountId);
    cs.setBigDecimal(2, amount);
    cs.execute();
} catch (SQLException e) {
    switch (e.getErrorCode()) {
        case 6550:
            // ORA-06550: compilation error in an anonymous block
            // Usually a bug in the dynamic SQL construction
            logger.error("PL/SQL compilation error", Map.of(
                "oraCode",  e.getErrorCode(),
                "message",  e.getMessage()  // Contains line, column, PLS code
            ));
            throw new ApplicationBugException("Dynamic PL/SQL compilation failed", e);

        case 4063:
            // ORA-04063: package body has errors
            // A stored object is INVALID — deployment or DDL issue
            // Do NOT retry — this will not resolve on its own
            logger.error("Database object INVALID — deployment issue", Map.of(
                "oraCode",  e.getErrorCode(),
                "message",  e.getMessage()
            ));
            alertingService.critical("ORA-04063: " + e.getMessage());
            throw new DeploymentException("Database object is invalid", e);

        case 6508:
            // ORA-06508: could not find program unit being called
            // The package or procedure does not exist or is INVALID
            logger.error("Program unit not found", Map.of(
                "oraCode",  e.getErrorCode(),
                "message",  e.getMessage()
            ));
            alertingService.critical("ORA-06508: " + e.getMessage());
            throw new DeploymentException("Program unit not found", e);

        default:
            throw e;
    }
}


============================================================
PYTHON / python-oracledb
============================================================

import oracledb

try:
    cursor.callproc('billing_pkg.process_payment', [account_id, amount])
except oracledb.DatabaseError as e:
    error = e.args[0]
    if error.code == 6550:
        # Compilation error in dynamic PL/SQL
        logger.error('PL/SQL compilation error', extra={
            'ora_code': error.code, 'message': error.message})
        raise ApplicationBugError('Dynamic PL/SQL failed') from e
    elif error.code in (4063, 6508):
        # Database object is INVALID — escalate immediately
        logger.error('Database object INVALID', extra={
            'ora_code': error.code, 'message': error.message})
        alert_team('critical', f'ORA-{error.code}: {error.message}')
        raise DeploymentError('Invalid database object') from e
    else:
        raise


============================================================
NODE.JS / node-oracledb
============================================================

try {
    await connection.execute(
        'BEGIN billing_pkg.process_payment(:id, :amt); END;',
        { id: accountId, amt: amount }
    );
} catch (err) {
    if (err.errorNum === 6550) {
        console.error(JSON.stringify({
            level: 'error', oraCode: err.errorNum,
            message: err.message, context: 'dynamic PL/SQL'
        }));
        throw new ApplicationBugError('Dynamic PL/SQL failed', { cause: err });
    } else if ([4063, 6508].includes(err.errorNum)) {
        console.error(JSON.stringify({
            level: 'critical', oraCode: err.errorNum,
            message: err.message
        }));
        await alertingService.critical(`ORA-${err.errorNum}: ${err.message}`);
        throw new DeploymentError('Invalid database object', { cause: err });
    } else {
        throw err;
    }
}


============================================================
Key points:
============================================================
1. ORA-06550: compilation error in dynamic PL/SQL — usually a code bug
2. ORA-04063: stored object is INVALID — deployment/DDL issue, escalate
3. ORA-06508: program unit not found — object missing or INVALID, escalate
4. Do NOT retry ORA-04063 or ORA-06508 — they will not self-resolve
5. Classify ORA-04063 as a deployment error, not an application error
6. Log the full Oracle error message — it contains the INVALID object name