ORA-06512 At Line - Trace PL/SQL Error Location and Stack

ORA-06512: At Line

Error Overview

Error Text: ORA-06512: at "schema.program_name", line number or ORA-06512: at line number

This error provides the call stack trace for PL/SQL exceptions, showing exactly where an error occurred and how it propagated through the call chain. It’s not an error itself but rather part of the error message that helps identify the source and path of exceptions in PL/SQL code.

Understanding Error Stack Traces

Stack Trace Components

Error Stack Structure
├── Original Error (ORA-xxxxx)
├── Propagation Path (ORA-06512 entries)
├── Line Numbers (specific code lines)
├── Program Units (procedures, functions, packages)
└── Call Sequence (from inner to outer calls)

Common Scenarios

• Exception propagation - Unhandled exceptions bubbling up
• Nested procedure calls - Errors in called procedures
• Package method chains - Errors across package boundaries
• Trigger exceptions - Errors in database triggers
• Function calls in SQL - Errors in functions called from SQL

Diagnostic Steps

1. Decode the Error Stack

-- Example of analyzing an error stack
/*
ORA-01403: no data found
ORA-06512: at "SCHEMA.EMPLOYEE_PKG", line 45
ORA-06512: at "SCHEMA.SALARY_CALC", line 23
ORA-06512: at line 8

This means:
1. NO_DATA_FOUND exception occurred
2. In EMPLOYEE_PKG at line 45
3. Which was called by SALARY_CALC at line 23
4. Which was called from anonymous block at line 8
*/

-- Create function to parse error stack
CREATE OR REPLACE FUNCTION parse_error_stack(
  p_error_stack VARCHAR2
) RETURN VARCHAR2 AS
  v_lines APEX_T_VARCHAR2;
  v_analysis VARCHAR2(4000);
  v_line VARCHAR2(500);
BEGIN
  -- Split into lines
  v_lines := APEX_STRING.SPLIT(p_error_stack, CHR(10));

  v_analysis := 'Error Stack Analysis:' || CHR(10);
  v_analysis := v_analysis || RPAD('=', 40, '=') || CHR(10);

  FOR i IN 1..v_lines.COUNT LOOP
    v_line := TRIM(v_lines(i));

    IF v_line LIKE 'ORA-%' AND NOT v_line LIKE 'ORA-06512%' THEN
      v_analysis := v_analysis || 'ROOT CAUSE: ' || v_line || CHR(10);
    ELSIF v_line LIKE 'ORA-06512%' THEN
      IF REGEXP_LIKE(v_line, 'at "[^"]+\.[^"]+"') THEN
        v_analysis := v_analysis || 'CALL STACK: ' ||
          REGEXP_SUBSTR(v_line, '"[^"]+\.[^"]+"') ||
          ' line ' ||
          REGEXP_SUBSTR(v_line, 'line \d+') || CHR(10);
      ELSE
        v_analysis := v_analysis || 'CALL STACK: Anonymous block ' ||
          REGEXP_SUBSTR(v_line, 'line \d+') || CHR(10);
      END IF;
    END IF;
  END LOOP;

  RETURN v_analysis;
END;
/

-- Function to get current call stack
CREATE OR REPLACE FUNCTION get_call_stack RETURN VARCHAR2 AS
BEGIN
  RETURN DBMS_UTILITY.FORMAT_CALL_STACK;
END;
/

-- Function to get error stack
CREATE OR REPLACE FUNCTION get_error_stack RETURN VARCHAR2 AS
BEGIN
  RETURN DBMS_UTILITY.FORMAT_ERROR_STACK;
END;
/

2. Locate Source Code

-- Find source code at specific line
CREATE OR REPLACE PROCEDURE show_source_at_line(
  p_object_name VARCHAR2,
  p_line_number NUMBER,
  p_context_lines NUMBER DEFAULT 5
) AS
  v_start_line NUMBER;
  v_end_line NUMBER;
BEGIN
  v_start_line := GREATEST(1, p_line_number - p_context_lines);
  v_end_line := p_line_number + p_context_lines;

  DBMS_OUTPUT.PUT_LINE('Source code for ' || p_object_name ||
                      ' around line ' || p_line_number || ':');
  DBMS_OUTPUT.PUT_LINE(RPAD('=', 60, '='));

  FOR rec IN (
    SELECT line, text,
           CASE WHEN line = p_line_number THEN '>>> ' ELSE '    ' END as marker
    FROM user_source
    WHERE name = UPPER(p_object_name)
      AND line BETWEEN v_start_line AND v_end_line
    ORDER BY line
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.marker ||
                        LPAD(rec.line, 3, '0') || ': ' ||
                        RTRIM(rec.text));
  END LOOP;
END;
/

-- Query to find all PL/SQL objects with potential issues
SELECT
  owner,
  name,
  type,
  line,
  text
FROM dba_source
WHERE (UPPER(text) LIKE '%SELECT%INTO%'  -- Potential NO_DATA_FOUND
    OR UPPER(text) LIKE '%RAISE%'
    OR UPPER(text) LIKE '%EXCEPTION%')
  AND owner = USER
  AND type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE BODY')
ORDER BY name, line;

3. Analyze Exception Handling

-- Check for proper exception handling
CREATE OR REPLACE PROCEDURE analyze_exception_handling(
  p_object_name VARCHAR2
) AS
  v_has_exception BOOLEAN := FALSE;
  v_has_when_others BOOLEAN := FALSE;
  v_exception_line NUMBER;
  v_line_count NUMBER := 0;
  v_unhandled_selects NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Exception Analysis for ' || p_object_name);
  DBMS_OUTPUT.PUT_LINE(RPAD('=', 50, '='));

  FOR rec IN (
    SELECT line, UPPER(text) as text
    FROM user_source
    WHERE name = UPPER(p_object_name)
      AND type = 'PROCEDURE'
    ORDER BY line
  ) LOOP
    v_line_count := v_line_count + 1;

    -- Check for exception section
    IF rec.text LIKE '%EXCEPTION%' THEN
      v_has_exception := TRUE;
      v_exception_line := rec.line;
    END IF;

    -- Check for WHEN OTHERS
    IF rec.text LIKE '%WHEN OTHERS%' THEN
      v_has_when_others := TRUE;
    END IF;

    -- Check for unhandled SELECT INTO
    IF rec.text LIKE '%SELECT%INTO%' THEN
      v_unhandled_selects := v_unhandled_selects + 1;
    END IF;
  END LOOP;

  -- Report findings
  DBMS_OUTPUT.PUT_LINE('Total lines of code: ' || v_line_count);
  DBMS_OUTPUT.PUT_LINE('Has exception section: ' ||
                      CASE WHEN v_has_exception THEN 'YES (line ' ||
                      v_exception_line || ')' ELSE 'NO' END);
  DBMS_OUTPUT.PUT_LINE('Has WHEN OTHERS: ' ||
                      CASE WHEN v_has_when_others THEN 'YES' ELSE 'NO' END);
  DBMS_OUTPUT.PUT_LINE('SELECT INTO statements: ' || v_unhandled_selects);

  -- Recommendations
  IF NOT v_has_exception THEN
    DBMS_OUTPUT.PUT_LINE('RECOMMENDATION: Add exception handling section');
  END IF;

  IF v_unhandled_selects > 0 AND NOT v_has_exception THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: SELECT INTO without exception handling');
  END IF;
END;
/

Immediate Solutions

Solution 1: Add Proper Exception Handling

Implement Exception Sections

-- Example of proper exception handling
CREATE OR REPLACE PROCEDURE safe_employee_lookup(
  p_employee_id NUMBER,
  p_salary OUT NUMBER
) AS
  v_count NUMBER;
BEGIN
  -- Check if employee exists first
  SELECT COUNT(*)
  INTO v_count
  FROM employees
  WHERE employee_id = p_employee_id;

  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee ID ' || p_employee_id || ' not found');
  END IF;

  -- Safe SELECT INTO with existence confirmed
  SELECT salary
  INTO p_salary
  FROM employees
  WHERE employee_id = p_employee_id;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found: ' || p_employee_id);
  WHEN TOO_MANY_ROWS THEN
    RAISE_APPLICATION_ERROR(-20002, 'Multiple employees with ID: ' || p_employee_id);
  WHEN OTHERS THEN
    -- Log the error with call stack
    DBMS_OUTPUT.PUT_LINE('Unexpected error in safe_employee_lookup');
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Call Stack: ' || DBMS_UTILITY.FORMAT_CALL_STACK);
    RAISE;  -- Re-raise the exception
END;
/

-- Template for robust exception handling
CREATE OR REPLACE PROCEDURE exception_handling_template AS
BEGIN
  -- Main logic here
  NULL;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Handle specific known exceptions
    RAISE_APPLICATION_ERROR(-20001, 'Required data not found');

  WHEN TOO_MANY_ROWS THEN
    RAISE_APPLICATION_ERROR(-20002, 'Query returned multiple rows');

  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(-20003, 'Duplicate key violation');

  WHEN VALUE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20004, 'Data type conversion error');

  WHEN OTHERS THEN
    -- Log detailed error information
    DBMS_OUTPUT.PUT_LINE('Unexpected error occurred');
    DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Error Stack: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
    DBMS_OUTPUT.PUT_LINE('Call Stack: ' || DBMS_UTILITY.FORMAT_CALL_STACK);

    -- Re-raise or handle as appropriate
    RAISE;
END;
/

Solution 2: Implement Error Logging

Create Error Logging Framework

-- Create error log table
CREATE TABLE error_log (
  error_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  error_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  username VARCHAR2(30) DEFAULT USER,
  program_name VARCHAR2(100),
  error_code NUMBER,
  error_message VARCHAR2(4000),
  call_stack CLOB,
  error_stack CLOB,
  sql_code NUMBER,
  session_info VARCHAR2(500)
);

-- Create error logging package
CREATE OR REPLACE PACKAGE error_logger AS

  PROCEDURE log_error(
    p_program_name VARCHAR2 DEFAULT NULL,
    p_additional_info VARCHAR2 DEFAULT NULL
  );

  PROCEDURE log_custom_error(
    p_program_name VARCHAR2,
    p_error_message VARCHAR2,
    p_error_code NUMBER DEFAULT NULL
  );

  FUNCTION get_session_info RETURN VARCHAR2;

END error_logger;
/

CREATE OR REPLACE PACKAGE BODY error_logger AS

  FUNCTION get_session_info RETURN VARCHAR2 AS
    v_info VARCHAR2(500);
  BEGIN
    SELECT 'SID=' || SID || ',SERIAL#=' || SERIAL# ||
           ',MODULE=' || NVL(MODULE, 'N/A') ||
           ',MACHINE=' || MACHINE || ',OSUSER=' || OSUSER
    INTO v_info
    FROM v$session
    WHERE AUDSID = USERENV('SESSIONID')
      AND ROWNUM = 1;

    RETURN v_info;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 'Session info unavailable';
  END get_session_info;

  PROCEDURE log_error(
    p_program_name VARCHAR2 DEFAULT NULL,
    p_additional_info VARCHAR2 DEFAULT NULL
  ) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_program VARCHAR2(100);
  BEGIN
    -- Extract program name from call stack if not provided
    IF p_program_name IS NULL THEN
      v_program := REGEXP_SUBSTR(
        DBMS_UTILITY.FORMAT_CALL_STACK,
        '"[^"]+\.[^"]+"',
        1, 2  -- Get the calling program, not this one
      );
    ELSE
      v_program := p_program_name;
    END IF;

    INSERT INTO error_log (
      program_name,
      error_code,
      error_message,
      call_stack,
      error_stack,
      sql_code,
      session_info
    ) VALUES (
      v_program || CASE WHEN p_additional_info IS NOT NULL
                        THEN ' (' || p_additional_info || ')'
                        ELSE '' END,
      SQLCODE,
      SQLERRM,
      DBMS_UTILITY.FORMAT_CALL_STACK,
      DBMS_UTILITY.FORMAT_ERROR_STACK,
      SQLCODE,
      get_session_info
    );

    COMMIT;
  END log_error;

  PROCEDURE log_custom_error(
    p_program_name VARCHAR2,
    p_error_message VARCHAR2,
    p_error_code NUMBER DEFAULT NULL
  ) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO error_log (
      program_name,
      error_code,
      error_message,
      call_stack,
      session_info
    ) VALUES (
      p_program_name,
      NVL(p_error_code, -20000),
      p_error_message,
      DBMS_UTILITY.FORMAT_CALL_STACK,
      get_session_info
    );

    COMMIT;
  END log_custom_error;

END error_logger;
/

-- Example usage in procedures
CREATE OR REPLACE PROCEDURE example_with_logging AS
  v_salary NUMBER;
BEGIN
  -- Some business logic
  SELECT salary INTO v_salary
  FROM employees
  WHERE employee_id = 999999;  -- Non-existent ID

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    error_logger.log_error('example_with_logging', 'Employee 999999 not found');
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');

  WHEN OTHERS THEN
    error_logger.log_error('example_with_logging');
    RAISE;
END;
/

Solution 3: Debug with Better Stack Traces

Enhanced Debugging Tools

-- Create debugging package
CREATE OR REPLACE PACKAGE debug_utils AS

  g_debug_enabled BOOLEAN := TRUE;

  PROCEDURE debug_print(p_message VARCHAR2);
  PROCEDURE enter_procedure(p_procedure_name VARCHAR2);
  PROCEDURE exit_procedure(p_procedure_name VARCHAR2);
  PROCEDURE print_call_stack;
  PROCEDURE print_error_context;

END debug_utils;
/

CREATE OR REPLACE PACKAGE BODY debug_utils AS

  TYPE call_stack_type IS TABLE OF VARCHAR2(100);
  g_call_stack call_stack_type := call_stack_type();

  PROCEDURE debug_print(p_message VARCHAR2) AS
  BEGIN
    IF g_debug_enabled THEN
      DBMS_OUTPUT.PUT_LINE('[DEBUG] ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF3') ||
                          ': ' || p_message);
    END IF;
  END debug_print;

  PROCEDURE enter_procedure(p_procedure_name VARCHAR2) AS
  BEGIN
    g_call_stack.EXTEND;
    g_call_stack(g_call_stack.COUNT) := p_procedure_name;
    debug_print('ENTER: ' || p_procedure_name || ' (depth=' || g_call_stack.COUNT || ')');
  END enter_procedure;

  PROCEDURE exit_procedure(p_procedure_name VARCHAR2) AS
  BEGIN
    debug_print('EXIT:  ' || p_procedure_name);
    IF g_call_stack.COUNT > 0 THEN
      g_call_stack.TRIM;
    END IF;
  END exit_procedure;

  PROCEDURE print_call_stack AS
  BEGIN
    debug_print('Current Call Stack:');
    FOR i IN 1..g_call_stack.COUNT LOOP
      debug_print('  ' || i || ': ' || g_call_stack(i));
    END LOOP;
  END print_call_stack;

  PROCEDURE print_error_context AS
  BEGIN
    debug_print('=== ERROR CONTEXT ===');
    debug_print('Error: ' || SQLERRM);
    debug_print('Error Stack:');
    debug_print(DBMS_UTILITY.FORMAT_ERROR_STACK);
    debug_print('Call Stack:');
    debug_print(DBMS_UTILITY.FORMAT_CALL_STACK);
    print_call_stack;
    debug_print('===================');
  END print_error_context;

END debug_utils;
/

-- Example procedure using debugging
CREATE OR REPLACE PROCEDURE complex_calculation(
  p_employee_id NUMBER,
  p_result OUT NUMBER
) AS
BEGIN
  debug_utils.enter_procedure('complex_calculation');

  debug_utils.debug_print('Processing employee ID: ' || p_employee_id);

  -- Simulate some complex logic
  SELECT salary * 1.1
  INTO p_result
  FROM employees
  WHERE employee_id = p_employee_id;

  debug_utils.debug_print('Calculation result: ' || p_result);
  debug_utils.exit_procedure('complex_calculation');

EXCEPTION
  WHEN OTHERS THEN
    debug_utils.print_error_context;
    debug_utils.exit_procedure('complex_calculation');
    RAISE;
END;
/

Long-Term Solutions

1. Standardized Error Handling Framework

Create Enterprise Error Management

-- Create exception hierarchy
CREATE OR REPLACE PACKAGE exception_manager AS

  -- Custom exception types
  business_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(business_error, -20001);

  data_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(data_error, -20002);

  system_error EXCEPTION;
  PRAGMA EXCEPTION_INIT(system_error, -20003);

  -- Error handling procedures
  PROCEDURE handle_business_error(
    p_error_msg VARCHAR2,
    p_program_name VARCHAR2 DEFAULT NULL
  );

  PROCEDURE handle_data_error(
    p_error_msg VARCHAR2,
    p_program_name VARCHAR2 DEFAULT NULL
  );

  PROCEDURE handle_system_error(
    p_error_msg VARCHAR2 DEFAULT NULL,
    p_program_name VARCHAR2 DEFAULT NULL
  );

  -- Standard exception handler
  PROCEDURE standard_exception_handler(
    p_program_name VARCHAR2,
    p_additional_context VARCHAR2 DEFAULT NULL
  );

END exception_manager;
/

CREATE OR REPLACE PACKAGE BODY exception_manager AS

  PROCEDURE handle_business_error(
    p_error_msg VARCHAR2,
    p_program_name VARCHAR2 DEFAULT NULL
  ) AS
  BEGIN
    error_logger.log_custom_error(
      NVL(p_program_name, 'Unknown'),
      'BUSINESS ERROR: ' || p_error_msg,
      -20001
    );
    RAISE_APPLICATION_ERROR(-20001, p_error_msg);
  END handle_business_error;

  PROCEDURE handle_data_error(
    p_error_msg VARCHAR2,
    p_program_name VARCHAR2 DEFAULT NULL
  ) AS
  BEGIN
    error_logger.log_custom_error(
      NVL(p_program_name, 'Unknown'),
      'DATA ERROR: ' || p_error_msg,
      -20002
    );
    RAISE_APPLICATION_ERROR(-20002, p_error_msg);
  END handle_data_error;

  PROCEDURE handle_system_error(
    p_error_msg VARCHAR2 DEFAULT NULL,
    p_program_name VARCHAR2 DEFAULT NULL
  ) AS
    v_error_msg VARCHAR2(4000);
  BEGIN
    v_error_msg := NVL(p_error_msg, 'System error: ' || SQLERRM);

    error_logger.log_error(p_program_name, v_error_msg);
    RAISE_APPLICATION_ERROR(-20003, v_error_msg);
  END handle_system_error;

  PROCEDURE standard_exception_handler(
    p_program_name VARCHAR2,
    p_additional_context VARCHAR2 DEFAULT NULL
  ) AS
  BEGIN
    CASE SQLCODE
      WHEN -1403 THEN  -- NO_DATA_FOUND
        handle_data_error('Required data not found', p_program_name);
      WHEN -1422 THEN  -- TOO_MANY_ROWS
        handle_data_error('Query returned multiple rows', p_program_name);
      WHEN -1 THEN     -- DUP_VAL_ON_INDEX
        handle_data_error('Duplicate value violation', p_program_name);
      WHEN -6502 THEN  -- VALUE_ERROR
        handle_data_error('Data type or size error', p_program_name);
      ELSE
        handle_system_error(SQLERRM, p_program_name);
    END CASE;
  END standard_exception_handler;

END exception_manager;
/

2. Code Quality Standards

Implement Code Review Standards

-- Create code quality checker
CREATE OR REPLACE PACKAGE code_quality_checker AS

  PROCEDURE check_exception_handling(p_object_name VARCHAR2);
  PROCEDURE check_all_procedures;
  PROCEDURE generate_quality_report(p_owner VARCHAR2 DEFAULT USER);

END code_quality_checker;
/

CREATE OR REPLACE PACKAGE BODY code_quality_checker AS

  TYPE quality_issue_rec IS RECORD (
    object_name VARCHAR2(30),
    issue_type VARCHAR2(50),
    line_number NUMBER,
    description VARCHAR2(500)
  );

  TYPE quality_issues_tab IS TABLE OF quality_issue_rec;

  PROCEDURE check_exception_handling(p_object_name VARCHAR2) AS
    v_has_exception BOOLEAN := FALSE;
    v_has_when_others BOOLEAN := FALSE;
    v_select_into_count NUMBER := 0;
    v_raise_count NUMBER := 0;
  BEGIN
    -- Analyze source code
    FOR rec IN (
      SELECT line, UPPER(text) as text
      FROM user_source
      WHERE name = UPPER(p_object_name)
        AND type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE BODY')
      ORDER BY line
    ) LOOP

      IF rec.text LIKE '%EXCEPTION%' THEN
        v_has_exception := TRUE;
      END IF;

      IF rec.text LIKE '%WHEN OTHERS%' THEN
        v_has_when_others := TRUE;
      END IF;

      IF rec.text LIKE '%SELECT%INTO%' THEN
        v_select_into_count := v_select_into_count + 1;
      END IF;

      IF rec.text LIKE '%RAISE%' THEN
        v_raise_count := v_raise_count + 1;
      END IF;

    END LOOP;

    -- Report findings
    DBMS_OUTPUT.PUT_LINE('Quality Check: ' || p_object_name);
    DBMS_OUTPUT.PUT_LINE('  Has exception section: ' ||
                        CASE WHEN v_has_exception THEN 'YES' ELSE 'NO' END);
    DBMS_OUTPUT.PUT_LINE('  Has WHEN OTHERS: ' ||
                        CASE WHEN v_has_when_others THEN 'YES' ELSE 'NO' END);
    DBMS_OUTPUT.PUT_LINE('  SELECT INTO statements: ' || v_select_into_count);
    DBMS_OUTPUT.PUT_LINE('  Explicit RAISE statements: ' || v_raise_count);

    -- Quality issues
    IF NOT v_has_exception AND v_select_into_count > 0 THEN
      DBMS_OUTPUT.PUT_LINE('  ⚠ WARNING: SELECT INTO without exception handling');
    END IF;

    IF v_has_when_others AND v_raise_count = 0 THEN
      DBMS_OUTPUT.PUT_LINE('  ⚠ WARNING: WHEN OTHERS without RAISE (swallowing exceptions)');
    END IF;

  END check_exception_handling;

  PROCEDURE check_all_procedures AS
  BEGIN
    FOR rec IN (
      SELECT DISTINCT name
      FROM user_source
      WHERE type IN ('PROCEDURE', 'FUNCTION')
      ORDER BY name
    ) LOOP
      check_exception_handling(rec.name);
      DBMS_OUTPUT.PUT_LINE('---');
    END LOOP;
  END check_all_procedures;

  PROCEDURE generate_quality_report(p_owner VARCHAR2 DEFAULT USER) AS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('CODE QUALITY REPORT');
    DBMS_OUTPUT.PUT_LINE('==================');
    DBMS_OUTPUT.PUT_LINE('Schema: ' || p_owner);
    DBMS_OUTPUT.PUT_LINE('Date: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
    DBMS_OUTPUT.PUT_LINE('');

    check_all_procedures;
  END generate_quality_report;

END code_quality_checker;
/

Prevention Strategies

1. Development Standards

-- Create development standards template
CREATE OR REPLACE PROCEDURE development_template AS
  v_parameter VARCHAR2(100);
  v_result NUMBER;
BEGIN
  -- Always validate input parameters
  IF v_parameter IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'Parameter cannot be null');
  END IF;

  -- Use explicit cursor when possible instead of SELECT INTO
  FOR rec IN (
    SELECT column1, column2
    FROM some_table
    WHERE condition = v_parameter
  ) LOOP
    -- Process each row
    v_result := rec.column1 * rec.column2;
  END LOOP;

  -- If you must use SELECT INTO, handle exceptions
  BEGIN
    SELECT single_value
    INTO v_result
    FROM some_table
    WHERE unique_condition = v_parameter;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_result := 0;  -- Or handle appropriately
    WHEN TOO_MANY_ROWS THEN
      RAISE_APPLICATION_ERROR(-20002, 'Multiple records found');
  END;

EXCEPTION
  WHEN OTHERS THEN
    -- Always log errors before re-raising
    error_logger.log_error('development_template');
    RAISE;  -- Don't swallow exceptions
END development_template;
/

2. Testing Framework

-- Create test framework for exception handling
CREATE OR REPLACE PACKAGE exception_test_framework AS

  PROCEDURE test_no_data_found_handling(
    p_procedure_name VARCHAR2,
    p_parameter VARCHAR2
  );

  PROCEDURE test_too_many_rows_handling(
    p_procedure_name VARCHAR2,
    p_parameter VARCHAR2
  );

  PROCEDURE run_all_exception_tests;

END exception_test_framework;
/

Related Errors

These errors commonly appear with ORA-06512:

• ORA-01403 - No data found
• ORA-01422 - Exact fetch returns more than requested
• ORA-06502 - PL/SQL numeric or value error
• ORA-20001 - User-defined errors

Quick Reference

Emergency Response Steps

1. ✓ Read the complete error stack from bottom to top
2. ✓ Identify the original error (first ORA-xxxxx)
3. ✓ Locate the source code at the specified line numbers
4. ✓ Check for missing exception handling
5. ✓ Add appropriate exception sections
6. ✓ Test error scenarios

Quick Commands

-- Get current call stack
SELECT DBMS_UTILITY.FORMAT_CALL_STACK FROM dual;

-- Get error stack in exception handler
SELECT DBMS_UTILITY.FORMAT_ERROR_STACK FROM dual;

-- Show source code at line
EXEC show_source_at_line('PROCEDURE_NAME', 25);

-- Check exception handling
EXEC code_quality_checker.check_exception_handling('PROCEDURE_NAME');

-- View source code
SELECT line, text FROM user_source WHERE name = 'PROCEDURE_NAME' ORDER BY line;

Prevention Guidelines

• Always handle exceptions - Add EXCEPTION sections to all procedures
• Use specific handlers - Handle known exceptions explicitly
• Log before re-raising - Don’t lose error context
• Validate parameters - Check inputs before processing
• Test error paths - Include exception scenarios in testing
• Use meaningful messages - Provide context in error messages