ORA-01403 No Data Found - Missing Data Handling

ORA-01403: No Data Found

Error Overview

Error Text: ORA-01403: no data found

This error occurs when a SELECT INTO statement returns no rows, or when a cursor fetch operation finds no data to retrieve. It’s one of the most common PL/SQL exceptions and typically indicates that an expected record doesn’t exist in the database or that query conditions are too restrictive.

Understanding NO_DATA_FOUND Exception

When This Error Occurs

NO_DATA_FOUND Scenarios
├── SELECT INTO statements - No rows match criteria
├── Cursor fetch operations - No more rows to fetch
├── Built-in function returns - Functions expecting data find none
├── Collection access - Accessing non-existent collection elements
└── Dynamic SQL execution - No rows returned from dynamic queries

Common Triggers

• Missing records - Query for data that doesn’t exist
• Wrong filter criteria - Conditions exclude all possible matches
• Data migration issues - Expected reference data is missing
• Timing issues - Data not yet committed by other transactions
• Case sensitivity - Exact match requirements not met

Diagnostic Steps

1. Analyze the Failing Query

-- Test the basic query to see if data exists
SELECT COUNT(*) as record_count
FROM target_table
WHERE condition_column = 'search_value';

-- Check if data exists with less restrictive conditions
SELECT COUNT(*) as total_records
FROM target_table;

-- Look for similar data (case sensitivity, whitespace)
SELECT
  condition_column,
  COUNT(*) as matches,
  CASE
    WHEN condition_column = 'search_value' THEN 'EXACT'
    WHEN UPPER(condition_column) = UPPER('search_value') THEN 'CASE_DIFFERENT'
    WHEN TRIM(condition_column) = TRIM('search_value') THEN 'WHITESPACE_DIFFERENT'
    ELSE 'VALUE_DIFFERENT'
  END as match_type
FROM target_table
WHERE UPPER(TRIM(condition_column)) = UPPER(TRIM('search_value'))
GROUP BY condition_column
ORDER BY match_type, condition_column;

-- Check for partial matches
SELECT
  condition_column,
  CASE
    WHEN condition_column LIKE '%search_value%' THEN 'CONTAINS'
    WHEN 'search_value' LIKE '%' || condition_column || '%' THEN 'CONTAINED_IN'
    ELSE 'NO_MATCH'
  END as match_type
FROM target_table
WHERE condition_column LIKE '%search_value%'
   OR 'search_value' LIKE '%' || condition_column || '%'
ORDER BY LENGTH(condition_column);

2. Check Data Completeness and Integrity

-- Check for NULL values in key columns
SELECT
  COUNT(*) as total_rows,
  COUNT(key_column) as non_null_key,
  COUNT(*) - COUNT(key_column) as null_key_count,
  ROUND((COUNT(key_column) / COUNT(*)) * 100, 2) as data_completeness_pct
FROM target_table;

-- Check for common data quality issues
SELECT
  'Empty strings' as issue_type,
  COUNT(*) as count
FROM target_table
WHERE key_column = ''
UNION ALL
SELECT
  'Only whitespace' as issue_type,
  COUNT(*) as count
FROM target_table
WHERE TRIM(key_column) IS NULL AND key_column IS NOT NULL
UNION ALL
SELECT
  'Leading/trailing spaces' as issue_type,
  COUNT(*) as count
FROM target_table
WHERE key_column != TRIM(key_column)
UNION ALL
SELECT
  'Mixed case issues' as issue_type,
  COUNT(*) as count
FROM target_table
WHERE key_column != UPPER(key_column) AND key_column != LOWER(key_column);

-- Check reference data integrity
SELECT
  'Missing foreign key references' as issue_type,
  COUNT(*) as count
FROM main_table m
WHERE NOT EXISTS (
  SELECT 1 FROM reference_table r
  WHERE r.id = m.reference_id
);

3. Investigate Timing and Transaction Issues

-- Check for uncommitted data in other sessions
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.status,
  t.start_time,
  t.used_ublk as undo_blocks
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
  AND s.username IS NOT NULL
ORDER BY t.start_time;

-- Check for locks that might affect data visibility
SELECT
  s.sid,
  s.serial#,
  s.username,
  o.object_name,
  l.mode_held,
  l.mode_requested
FROM v$locked_object lo, dba_objects o, v$session s, v$lock l
WHERE lo.object_id = o.object_id
  AND lo.session_id = s.sid
  AND s.sid = l.sid
  AND o.object_name = UPPER('target_table');

-- Check isolation level and read consistency
SELECT
  SYS_CONTEXT('USERENV', 'CURRENT_SCN') as current_scn,
  (SELECT MAX(scn) FROM v$database_block_corruption) as corruption_scn
FROM dual;

-- Check for recent DDL that might affect data
SELECT
  owner,
  object_name,
  object_type,
  last_ddl_time,
  status
FROM dba_objects
WHERE object_name = UPPER('target_table')
   OR object_name LIKE '%' || UPPER('target_table') || '%'
ORDER BY last_ddl_time DESC;

4. Validate Query Logic and Conditions

-- Break down complex WHERE conditions to identify the restrictive part
-- Example for a multi-condition query
SELECT 'All conditions' as test_case, COUNT(*) as matches
FROM target_table
WHERE condition1 = 'value1'
  AND condition2 = 'value2'
  AND condition3 = 'value3'
UNION ALL
SELECT 'Condition 1 only', COUNT(*)
FROM target_table
WHERE condition1 = 'value1'
UNION ALL
SELECT 'Condition 2 only', COUNT(*)
FROM target_table
WHERE condition2 = 'value2'
UNION ALL
SELECT 'Condition 3 only', COUNT(*)
FROM target_table
WHERE condition3 = 'value3'
UNION ALL
SELECT 'Conditions 1+2', COUNT(*)
FROM target_table
WHERE condition1 = 'value1'
  AND condition2 = 'value2'
ORDER BY test_case;

-- Check for date/timestamp comparison issues
SELECT
  date_column,
  TO_CHAR(date_column, 'YYYY-MM-DD HH24:MI:SS') as formatted_date,
  CASE
    WHEN date_column = DATE '2024-01-01' THEN 'EXACT_DATE_MATCH'
    WHEN TRUNC(date_column) = DATE '2024-01-01' THEN 'DATE_PART_MATCH'
    WHEN date_column >= DATE '2024-01-01'
     AND date_column < DATE '2024-01-02' THEN 'DATE_RANGE_MATCH'
    ELSE 'NO_MATCH'
  END as match_type
FROM target_table
WHERE TRUNC(date_column) = DATE '2024-01-01'
ORDER BY date_column;

Immediate Solutions

Solution 1: Proper Exception Handling in PL/SQL

Basic Exception Handling Pattern

-- BAD: Unhandled NO_DATA_FOUND exception
CREATE OR REPLACE PROCEDURE get_employee_bad(
  p_employee_id NUMBER,
  p_employee_name OUT VARCHAR2
) AS
BEGIN
  SELECT first_name || ' ' || last_name
  INTO p_employee_name
  FROM employees
  WHERE employee_id = p_employee_id;
  -- Will raise ORA-01403 if employee doesn't exist
END;
/

-- GOOD: Proper exception handling
CREATE OR REPLACE PROCEDURE get_employee_good(
  p_employee_id NUMBER,
  p_employee_name OUT VARCHAR2,
  p_found OUT BOOLEAN
) AS
BEGIN
  SELECT first_name || ' ' || last_name
  INTO p_employee_name
  FROM employees
  WHERE employee_id = p_employee_id;

  p_found := TRUE;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_employee_name := NULL;
    p_found := FALSE;
  WHEN OTHERS THEN
    p_employee_name := NULL;
    p_found := FALSE;
    -- Log the unexpected error
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    RAISE;
END;
/

-- Alternative: Using cursor approach
CREATE OR REPLACE FUNCTION get_employee_safe(p_employee_id NUMBER)
RETURN VARCHAR2 AS
  v_employee_name VARCHAR2(200);

  CURSOR emp_cursor IS
    SELECT first_name || ' ' || last_name as full_name
    FROM employees
    WHERE employee_id = p_employee_id;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor INTO v_employee_name;

  IF emp_cursor%NOTFOUND THEN
    v_employee_name := 'Employee not found';
  END IF;

  CLOSE emp_cursor;
  RETURN v_employee_name;
END;
/

Advanced Exception Handling with Logging

-- Create error logging table
CREATE TABLE error_log (
  error_id NUMBER PRIMARY KEY,
  error_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  procedure_name VARCHAR2(100),
  error_code NUMBER,
  error_message VARCHAR2(1000),
  context_info VARCHAR2(2000),
  username VARCHAR2(30)
);

CREATE SEQUENCE error_log_seq START WITH 1 INCREMENT BY 1;

-- Comprehensive error handling procedure
CREATE OR REPLACE PROCEDURE get_customer_details(
  p_customer_id NUMBER,
  p_customer_record OUT customers%ROWTYPE,
  p_status OUT VARCHAR2
) AS
  v_procedure_name CONSTANT VARCHAR2(30) := 'get_customer_details';
  v_context VARCHAR2(1000);
BEGIN
  v_context := 'Searching for customer_id: ' || p_customer_id;

  -- Validate input
  IF p_customer_id IS NULL THEN
    p_status := 'ERROR: Customer ID cannot be null';
    RETURN;
  END IF;

  IF p_customer_id <= 0 THEN
    p_status := 'ERROR: Customer ID must be positive';
    RETURN;
  END IF;

  -- Attempt to fetch customer
  SELECT *
  INTO p_customer_record
  FROM customers
  WHERE customer_id = p_customer_id;

  p_status := 'SUCCESS';

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Clear output record
    p_customer_record := NULL;
    p_status := 'NOT_FOUND';

    -- Log the incident for tracking
    INSERT INTO error_log (
      error_id, procedure_name, error_code, error_message, context_info, username
    ) VALUES (
      error_log_seq.NEXTVAL, v_procedure_name, 1403, 'Customer not found', v_context, USER
    );
    COMMIT;

  WHEN TOO_MANY_ROWS THEN
    p_customer_record := NULL;
    p_status := 'ERROR: Multiple customers found with same ID';

    INSERT INTO error_log (
      error_id, procedure_name, error_code, error_message, context_info, username
    ) VALUES (
      error_log_seq.NEXTVAL, v_procedure_name, 1422, 'Multiple rows returned', v_context, USER
    );
    COMMIT;

  WHEN OTHERS THEN
    p_customer_record := NULL;
    p_status := 'ERROR: Unexpected database error';

    INSERT INTO error_log (
      error_id, procedure_name, error_code, error_message, context_info, username
    ) VALUES (
      error_log_seq.NEXTVAL, v_procedure_name, SQLCODE, SQLERRM, v_context, USER
    );
    COMMIT;
    RAISE;
END;
/

Solution 2: Use Defensive Programming Patterns

Existence Checks Before Operations

-- Function to check if record exists
CREATE OR REPLACE FUNCTION record_exists(
  p_table_name VARCHAR2,
  p_where_clause VARCHAR2
) RETURN BOOLEAN AS
  v_count NUMBER;
  v_sql VARCHAR2(1000);
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || p_table_name || ' WHERE ' || p_where_clause;
  EXECUTE IMMEDIATE v_sql INTO v_count;
  RETURN v_count > 0;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END;
/

-- Safe data retrieval with existence check
CREATE OR REPLACE PROCEDURE get_order_details(
  p_order_id NUMBER,
  p_order_record OUT orders%ROWTYPE,
  p_exists OUT BOOLEAN
) AS
BEGIN
  -- First check if order exists
  SELECT COUNT(*) INTO v_count
  FROM orders
  WHERE order_id = p_order_id;

  IF v_count = 0 THEN
    p_exists := FALSE;
    p_order_record := NULL;
    RETURN;
  END IF;

  -- Safe to fetch since we know it exists
  SELECT *
  INTO p_order_record
  FROM orders
  WHERE order_id = p_order_id;

  p_exists := TRUE;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- This shouldn't happen due to our check, but just in case
    p_exists := FALSE;
    p_order_record := NULL;
  WHEN TOO_MANY_ROWS THEN
    -- Data integrity issue - multiple orders with same ID
    RAISE_APPLICATION_ERROR(-20001, 'Data integrity error: Multiple orders found for ID ' || p_order_id);
END;
/

-- Using MERGE for upsert operations (avoids NO_DATA_FOUND)
CREATE OR REPLACE PROCEDURE upsert_customer_preference(
  p_customer_id NUMBER,
  p_preference_type VARCHAR2,
  p_preference_value VARCHAR2
) AS
BEGIN
  MERGE INTO customer_preferences cp
  USING (
    SELECT
      p_customer_id as customer_id,
      p_preference_type as preference_type,
      p_preference_value as preference_value
    FROM dual
  ) src
  ON (cp.customer_id = src.customer_id AND cp.preference_type = src.preference_type)
  WHEN MATCHED THEN
    UPDATE SET
      preference_value = src.preference_value,
      last_updated = SYSDATE
  WHEN NOT MATCHED THEN
    INSERT (customer_id, preference_type, preference_value, created_date)
    VALUES (src.customer_id, src.preference_type, src.preference_value, SYSDATE);

  COMMIT;
END;
/

NULL-Safe Comparisons and Default Values

-- Function with default values for missing data
CREATE OR REPLACE FUNCTION get_customer_credit_limit(
  p_customer_id NUMBER,
  p_default_limit NUMBER DEFAULT 1000
) RETURN NUMBER AS
  v_credit_limit NUMBER;
BEGIN
  SELECT NVL(credit_limit, p_default_limit)
  INTO v_credit_limit
  FROM customers
  WHERE customer_id = p_customer_id;

  RETURN v_credit_limit;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN p_default_limit;
  WHEN OTHERS THEN
    -- Log error and return safe default
    INSERT INTO error_log (error_id, procedure_name, error_code, error_message, context_info, username)
    VALUES (error_log_seq.NEXTVAL, 'get_customer_credit_limit', SQLCODE, SQLERRM,
            'customer_id: ' || p_customer_id, USER);
    COMMIT;
    RETURN p_default_limit;
END;
/

-- Safe string operations with NVL
CREATE OR REPLACE FUNCTION get_customer_display_name(p_customer_id NUMBER)
RETURN VARCHAR2 AS
  v_display_name VARCHAR2(200);
BEGIN
  SELECT
    CASE
      WHEN first_name IS NOT NULL AND last_name IS NOT NULL THEN
        first_name || ' ' || last_name
      WHEN company_name IS NOT NULL THEN
        company_name
      WHEN email IS NOT NULL THEN
        email
      ELSE
        'Customer #' || customer_id
    END
  INTO v_display_name
  FROM customers
  WHERE customer_id = p_customer_id;

  RETURN NVL(v_display_name, 'Unknown Customer');

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 'Customer Not Found';
  WHEN OTHERS THEN
    RETURN 'Error Retrieving Customer';
END;
/

Solution 3: Application-Level Data Validation

Java Exception Handling for NO_DATA_FOUND

// Java example with proper handling of NO_DATA_FOUND
public class CustomerService {

    public Customer getCustomer(int customerId) throws CustomerNotFoundException {
        String sql = "SELECT customer_id, first_name, last_name, email FROM customers WHERE customer_id = ?";

        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, customerId);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return new Customer(
                        rs.getInt("customer_id"),
                        rs.getString("first_name"),
                        rs.getString("last_name"),
                        rs.getString("email")
                    );
                } else {
                    throw new CustomerNotFoundException("Customer with ID " + customerId + " not found");
                }
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 1403) { // ORA-01403
                throw new CustomerNotFoundException("Customer with ID " + customerId + " not found", e);
            } else {
                throw new DatabaseException("Error retrieving customer", e);
            }
        }
    }

    public Optional<Customer> getCustomerOptional(int customerId) {
        try {
            return Optional.of(getCustomer(customerId));
        } catch (CustomerNotFoundException e) {
            return Optional.empty();
        }
    }

    public Customer getCustomerWithDefault(int customerId) {
        return getCustomerOptional(customerId)
            .orElse(new Customer(customerId, "Unknown", "Customer", "[email protected]"));
    }

    // Batch retrieval with missing data handling
    public Map<Integer, Customer> getCustomers(List<Integer> customerIds) {
        Map<Integer, Customer> customers = new HashMap<>();

        String sql = "SELECT customer_id, first_name, last_name, email FROM customers WHERE customer_id = ?";

        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            for (Integer customerId : customerIds) {
                pstmt.setInt(1, customerId);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        customers.put(customerId, new Customer(
                            rs.getInt("customer_id"),
                            rs.getString("first_name"),
                            rs.getString("last_name"),
                            rs.getString("email")
                        ));
                    } else {
                        // Log missing customer but continue processing
                        logger.warn("Customer not found: " + customerId);
                    }
                }
            }
        } catch (SQLException e) {
            throw new DatabaseException("Error retrieving customers", e);
        }

        return customers;
    }
}

Long-Term Solutions

1. Implement Data Validation and Quality Checks

Comprehensive Data Quality Framework

-- Create data quality monitoring table
CREATE TABLE data_quality_checks (
  check_id NUMBER PRIMARY KEY,
  table_name VARCHAR2(30),
  check_name VARCHAR2(100),
  check_sql VARCHAR2(4000),
  expected_result VARCHAR2(20), -- 'NO_ROWS', 'HAS_ROWS', 'COUNT_EQUALS', etc.
  expected_value NUMBER,
  severity VARCHAR2(10), -- 'ERROR', 'WARNING', 'INFO'
  active CHAR(1) DEFAULT 'Y'
);

-- Create data quality results table
CREATE TABLE data_quality_results (
  result_id NUMBER PRIMARY KEY,
  check_id NUMBER,
  check_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  actual_result VARCHAR2(20),
  actual_value NUMBER,
  status VARCHAR2(10), -- 'PASS', 'FAIL'
  details VARCHAR2(1000)
);

-- Data quality monitoring procedure
CREATE OR REPLACE PROCEDURE run_data_quality_checks AS
  CURSOR check_cursor IS
    SELECT check_id, table_name, check_name, check_sql, expected_result, expected_value, severity
    FROM data_quality_checks
    WHERE active = 'Y';

  v_actual_value NUMBER;
  v_status VARCHAR2(10);
  v_details VARCHAR2(1000);
BEGIN
  FOR check_rec IN check_cursor LOOP
    BEGIN
      -- Execute the quality check
      EXECUTE IMMEDIATE check_rec.check_sql INTO v_actual_value;

      -- Evaluate result
      CASE check_rec.expected_result
        WHEN 'NO_ROWS' THEN
          IF v_actual_value = 0 THEN
            v_status := 'PASS';
            v_details := 'No problematic rows found';
          ELSE
            v_status := 'FAIL';
            v_details := v_actual_value || ' problematic rows found';
          END IF;

        WHEN 'HAS_ROWS' THEN
          IF v_actual_value > 0 THEN
            v_status := 'PASS';
            v_details := v_actual_value || ' rows found as expected';
          ELSE
            v_status := 'FAIL';
            v_details := 'No rows found - expected data missing';
          END IF;

        WHEN 'COUNT_EQUALS' THEN
          IF v_actual_value = check_rec.expected_value THEN
            v_status := 'PASS';
            v_details := 'Count matches expected value';
          ELSE
            v_status := 'FAIL';
            v_details := 'Expected ' || check_rec.expected_value || ', found ' || v_actual_value;
          END IF;
      END CASE;

      -- Log result
      INSERT INTO data_quality_results (
        result_id, check_id, actual_result, actual_value, status, details
      ) VALUES (
        data_quality_seq.NEXTVAL, check_rec.check_id, check_rec.expected_result,
        v_actual_value, v_status, v_details
      );

      -- Alert on failures
      IF v_status = 'FAIL' AND check_rec.severity = 'ERROR' THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: ' || check_rec.check_name || ' - ' || v_details);
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO data_quality_results (
          result_id, check_id, status, details
        ) VALUES (
          data_quality_seq.NEXTVAL, check_rec.check_id, 'ERROR',
          'Check failed: ' || SQLERRM
        );
    END;
  END LOOP;

  COMMIT;
END;
/

-- Example data quality checks
INSERT INTO data_quality_checks (check_id, table_name, check_name, check_sql, expected_result, severity)
VALUES (1, 'CUSTOMERS', 'Missing customer email',
        'SELECT COUNT(*) FROM customers WHERE email IS NULL OR email = ''''',
        'NO_ROWS', 'WARNING');

INSERT INTO data_quality_checks (check_id, table_name, check_name, check_sql, expected_result, severity)
VALUES (2, 'ORDERS', 'Orders without customer reference',
        'SELECT COUNT(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id)',
        'NO_ROWS', 'ERROR');

2. Implement Reference Data Management

Master Data Management System

-- Create reference data management tables
CREATE TABLE reference_tables (
  table_name VARCHAR2(30) PRIMARY KEY,
  description VARCHAR2(200),
  load_frequency VARCHAR2(20), -- 'DAILY', 'WEEKLY', 'ON_DEMAND'
  last_loaded TIMESTAMP,
  source_system VARCHAR2(50),
  contact_person VARCHAR2(100)
);

CREATE TABLE reference_data_dependencies (
  parent_table VARCHAR2(30),
  child_table VARCHAR2(30),
  relationship_type VARCHAR2(20), -- 'REQUIRED', 'OPTIONAL'
  PRIMARY KEY (parent_table, child_table)
);

-- Procedure to validate reference data completeness
CREATE OR REPLACE PROCEDURE validate_reference_data AS
  CURSOR ref_table_cursor IS
    SELECT table_name, description
    FROM reference_tables;

  v_count NUMBER;
  v_issues NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== Reference Data Validation ===');

  FOR ref_rec IN ref_table_cursor LOOP
    -- Check if reference table has data
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || ref_rec.table_name INTO v_count;

    IF v_count = 0 THEN
      DBMS_OUTPUT.PUT_LINE('WARNING: ' || ref_rec.table_name || ' is empty');
      v_issues := v_issues + 1;
    ELSE
      DBMS_OUTPUT.PUT_LINE('OK: ' || ref_rec.table_name || ' has ' || v_count || ' records');
    END IF;

    -- Check dependencies
    FOR dep_rec IN (
      SELECT child_table, relationship_type
      FROM reference_data_dependencies
      WHERE parent_table = ref_rec.table_name
    ) LOOP
      -- Check for orphaned records
      EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM ' || dep_rec.child_table || ' c ' ||
        'WHERE NOT EXISTS (SELECT 1 FROM ' || ref_rec.table_name || ' p ' ||
        'WHERE p.id = c.' || LOWER(ref_rec.table_name) || '_id)'
      INTO v_count;

      IF v_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: ' || v_count || ' orphaned records in ' || dep_rec.child_table);
        v_issues := v_issues + 1;
      END IF;
    END LOOP;
  END LOOP;

  IF v_issues = 0 THEN
    DBMS_OUTPUT.PUT_LINE('=== All reference data validation passed ===');
  ELSE
    DBMS_OUTPUT.PUT_LINE('=== ' || v_issues || ' issues found ===');
  END IF;
END;
/

3. Application Architecture Improvements

Implement Caching and Data Preloading

// Java example with caching to reduce NO_DATA_FOUND scenarios
@Service
public class CachedCustomerService {

    private final Cache<Integer, Customer> customerCache;
    private final CustomerRepository customerRepository;

    public CachedCustomerService(CustomerRepository customerRepository) {
        this.customerRepository = customerRepository;
        this.customerCache = Caffeine.newBuilder()
            .maximumSize(10000)
            .expireAfterWrite(30, TimeUnit.MINUTES)
            .recordStats()
            .build();
    }

    public Optional<Customer> getCustomer(int customerId) {
        return Optional.ofNullable(
            customerCache.get(customerId, this::loadCustomerFromDatabase)
        );
    }

    private Customer loadCustomerFromDatabase(int customerId) {
        try {
            return customerRepository.findById(customerId);
        } catch (CustomerNotFoundException e) {
            // Cache negative results to avoid repeated database hits
            return null;
        }
    }

    // Preload commonly accessed customers
    @Scheduled(fixedRate = 300000) // Every 5 minutes
    public void preloadActiveCustomers() {
        List<Integer> activeCustomerIds = customerRepository.getActiveCustomerIds();

        for (Integer customerId : activeCustomerIds) {
            customerCache.get(customerId, this::loadCustomerFromDatabase);
        }

        logger.info("Preloaded {} active customers", activeCustomerIds.size());
    }

    // Validate reference data on application startup
    @PostConstruct
    public void validateReferenceData() {
        List<String> missingTables = new ArrayList<>();

        if (!customerRepository.hasCustomers()) {
            missingTables.add("customers");
        }

        if (!referenceDataService.hasCountries()) {
            missingTables.add("countries");
        }

        if (!missingTables.isEmpty()) {
            throw new ApplicationStartupException(
                "Missing reference data in tables: " + String.join(", ", missingTables)
            );
        }
    }
}

4. Database Design Improvements

Implement Data Seeding and Validation

-- Create data seeding framework
CREATE TABLE data_seeds (
  seed_id NUMBER PRIMARY KEY,
  table_name VARCHAR2(30),
  seed_data CLOB,
  seed_type VARCHAR2(20), -- 'INSERT', 'MERGE', 'SCRIPT'
  dependencies VARCHAR2(500), -- Comma-separated list of prerequisite seeds
  version VARCHAR2(10),
  applied_date TIMESTAMP,
  status VARCHAR2(20) -- 'PENDING', 'APPLIED', 'FAILED'
);

-- Data seeding procedure
CREATE OR REPLACE PROCEDURE apply_data_seeds AS
  CURSOR seed_cursor IS
    SELECT seed_id, table_name, seed_data, seed_type, dependencies
    FROM data_seeds
    WHERE status = 'PENDING'
    ORDER BY seed_id;

  v_dependencies_met BOOLEAN;
  v_dependency_list apex_t_varchar2;
BEGIN
  FOR seed_rec IN seed_cursor LOOP
    v_dependencies_met := TRUE;

    -- Check if dependencies are satisfied
    IF seed_rec.dependencies IS NOT NULL THEN
      v_dependency_list := apex_string.split(seed_rec.dependencies, ',');

      FOR i IN 1..v_dependency_list.COUNT LOOP
        IF NOT EXISTS (
          SELECT 1 FROM data_seeds
          WHERE seed_id = TO_NUMBER(TRIM(v_dependency_list(i)))
            AND status = 'APPLIED'
        ) THEN
          v_dependencies_met := FALSE;
          EXIT;
        END IF;
      END LOOP;
    END IF;

    IF v_dependencies_met THEN
      BEGIN
        -- Apply the seed
        IF seed_rec.seed_type = 'INSERT' THEN
          EXECUTE IMMEDIATE seed_rec.seed_data;
        ELSIF seed_rec.seed_type = 'MERGE' THEN
          EXECUTE IMMEDIATE seed_rec.seed_data;
        END IF;

        -- Mark as applied
        UPDATE data_seeds
        SET status = 'APPLIED', applied_date = SYSTIMESTAMP
        WHERE seed_id = seed_rec.seed_id;

        COMMIT;

      EXCEPTION
        WHEN OTHERS THEN
          UPDATE data_seeds
          SET status = 'FAILED'
          WHERE seed_id = seed_rec.seed_id;
          COMMIT;

          DBMS_OUTPUT.PUT_LINE('Seed ' || seed_rec.seed_id || ' failed: ' || SQLERRM);
      END;
    END IF;
  END LOOP;
END;
/

Related Errors

• ORA-01422 - Exact fetch returns more than requested number of rows
• ORA-06511 - PL/SQL: cursor already open
• ORA-01001 - Invalid cursor
• ORA-00100 - No data found (SQL*Plus specific)

Quick Reference

Emergency Response Steps

1. ✓ Verify the query returns data with broader criteria
2. ✓ Check for case sensitivity and whitespace issues
3. ✓ Validate input parameters and conditions
4. ✓ Add proper exception handling to catch NO_DATA_FOUND
5. ✓ Implement existence checks before SELECT INTO
6. ✓ Use cursor-based approaches for safer data retrieval

Quick Commands

-- Test if data exists
SELECT COUNT(*) FROM table_name WHERE condition;

-- Safe SELECT with default
SELECT NVL((SELECT column FROM table WHERE condition), 'default_value') FROM dual;

-- Using cursor to avoid exception
DECLARE
  CURSOR c IS SELECT column FROM table WHERE condition;
  v_value VARCHAR2(100);
BEGIN
  OPEN c;
  FETCH c INTO v_value;
  IF c%NOTFOUND THEN
    v_value := 'Not found';
  END IF;
  CLOSE c;
END;

-- Exception handling pattern
BEGIN
  SELECT column INTO variable FROM table WHERE condition;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    variable := 'default_value';
END;

Prevention Guidelines

• Always handle NO_DATA_FOUND - Use proper exception handling
• Validate inputs - Check parameters before querying
• Use existence checks - Verify data exists before SELECT INTO
• Implement defaults - Provide fallback values for missing data
• Use cursor-based approaches - Safer than SELECT INTO for optional data
• Monitor data quality - Regular checks for missing reference data