Program Compilation Errors (derror.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script displays compilation errors for PL/SQL objects including procedures, functions, packages, triggers, and types. It provides detailed error information with line numbers and positions to help developers debug and fix compilation issues in database objects.

Script

rem  derror.sql
rem
ttitle 'Program Errors'
rem
col owner format a12 heading 'OWNER'
col name format a30 heading 'NAME'
col type format a12 heading 'TYPE'
col sequence format 999 heading 'SEQ'
col line format 99999 heading 'LINE'
col position format 999 heading 'POS'
col text format a80 heading 'TEXT'
rem
select owner, name, type, sequence, line, position, text
  from sys.dba_errors
 where owner like upper('&owner')
   and name like upper('&name')
 order by owner, name, sequence;

Usage

-- Run the script in SQL*Plus or SQLcl
@derror.sql

-- When prompted, enter parameters:
-- owner: Schema owner pattern (% for all)
-- name: Object name pattern (% for all)

-- Examples
Enter value for owner: %           -- All schemas
Enter value for name: %            -- All objects

Enter value for owner: HR          -- Specific schema
Enter value for name: EMP_TRIGGER  -- Specific object

Parameters

• &owner: Schema owner pattern (use % for all schemas)
• &name: Object name pattern (use % for all objects)

Required Privileges

• SELECT on SYS.DBA_ERRORS
• Typically requires DBA role or explicit grants

Sample Output

Program Errors

OWNER        NAME                           TYPE         SEQ  LINE POS TEXT
------------ ------------------------------ ------------ --- ----- --- ----------------
HR           CALCULATE_SALARY               FUNCTION       1    15   5 PLS-00201: identifier 'INVALID_VAR' must be declared
HR           CALCULATE_SALARY               FUNCTION       2    20  10 PL/SQL: Statement ignored

SALES        UPDATE_COMMISSION              PROCEDURE      1     8  15 ORA-00942: table or view does not exist
SALES        UPDATE_COMMISSION              PROCEDURE      2    12   1 PL/SQL: SQL Statement ignored

HR           EMPLOYEE_AUDIT_TRG             TRIGGER        1     5  20 PLS-00103: Encountered the symbol ";" when expecting one of the following:
HR           EMPLOYEE_AUDIT_TRG             TRIGGER        2     5  20 ( * & = - + < / > mod rem and or

Key Output Columns

• OWNER: Schema owner of the object
• NAME: Name of the PL/SQL object with errors
• TYPE: Object type (PROCEDURE, FUNCTION, PACKAGE, TRIGGER, etc.)
• SEQ: Error sequence number
• LINE: Line number where error occurs
• POS: Position in line where error occurs
• TEXT: Error message description

Understanding Compilation Errors

Common Error Types

• PLS-00201: Identifier must be declared (variable/function not found)
• ORA-00942: Table or view does not exist
• PLS-00103: Syntax error - unexpected symbol
• PLS-00320: Declaration of this type must be incomplete
• ORA-06550: Line/column syntax error

Object Types with Errors

• PROCEDURE: Stored procedures
• FUNCTION: Functions
• PACKAGE: Package specifications and bodies
• TRIGGER: Database triggers
• TYPE: Object types
• JAVA SOURCE: Java stored procedures

Performance Analysis

Error Impact Assessment

• Invalid objects: Cannot be executed
• Dependent objects: May cascade invalidation
• Performance impact: Recompilation overhead
• Application errors: Runtime failures

Error Pattern Analysis

-- Most common error types
SELECT SUBSTR(text, 1, 20) error_pattern,
       COUNT(*) error_count
FROM dba_errors
GROUP BY SUBSTR(text, 1, 20)
ORDER BY error_count DESC;

Common Use Cases

Development Debugging

-- Check errors for specific schema
@derror.sql
-- Enter schema name and %
-- Fix compilation errors systematically

Deployment Validation

-- Check for errors after deployment
@derror.sql
-- Enter % for both parameters
-- Verify all objects compiled successfully

Dependency Troubleshooting

-- Find objects with dependency errors
@derror.sql
-- Look for ORA-00942 and similar dependency errors
-- Resolve in dependency order

Troubleshooting Compilation Errors

Systematic Error Resolution

1. Check error details:

   @derror.sql
   -- Note line numbers and error types

2. View object source:

   SELECT text FROM dba_source
   WHERE owner = 'HR' AND name = 'CALCULATE_SALARY'
   ORDER BY line;

3. Fix and recompile:

   ALTER FUNCTION hr.calculate_salary COMPILE;

Dependency Resolution

-- Check object dependencies
SELECT owner, name, type, referenced_owner, referenced_name
FROM dba_dependencies
WHERE owner = 'HR' AND name = 'CALCULATE_SALARY'
ORDER BY referenced_owner, referenced_name;

-- Compile dependencies first
ALTER TABLE hr.employees COMPILE;
ALTER FUNCTION hr.get_department COMPILE;
ALTER FUNCTION hr.calculate_salary COMPILE;

Mass Compilation

-- Compile all invalid objects
EXEC DBMS_UTILITY.COMPILE_SCHEMA('HR');

-- Or use UTL_RECOMP for parallel compilation
EXEC UTL_RECOMP.RECOMP_PARALLEL();

Advanced Analysis

Error Trend Analysis

-- Group errors by type and object
SELECT owner, type,
       COUNT(DISTINCT name) objects_with_errors,
       COUNT(*) total_errors
FROM dba_errors
GROUP BY owner, type
ORDER BY total_errors DESC;

Cross-Reference with Invalid Objects

-- Compare with DBA_OBJECTS status
SELECT e.owner, e.name, e.type,
       o.status object_status,
       COUNT(*) error_count
FROM dba_errors e, dba_objects o
WHERE e.owner = o.owner
AND e.name = o.object_name
AND e.type = o.object_type
GROUP BY e.owner, e.name, e.type, o.status
ORDER BY error_count DESC;

Dependency Chain Analysis

-- Find compilation order for dependent objects
WITH dep_tree AS (
  SELECT owner, name, type,
         referenced_owner, referenced_name, referenced_type,
         LEVEL compilation_level
  FROM dba_dependencies
  WHERE owner = 'HR'
  START WITH referenced_name IS NULL
  CONNECT BY PRIOR owner = referenced_owner
         AND PRIOR name = referenced_name
         AND PRIOR type = referenced_type
)
SELECT compilation_level, owner, name, type
FROM dep_tree
WHERE (owner, name, type) IN (
  SELECT owner, name, type FROM dba_errors
)
ORDER BY compilation_level, owner, name;

Error Resolution Strategies

Common Error Fixes

PLS-00201: Identifier not declared

-- Check if variable/function exists
-- Add declaration or fix spelling
DECLARE
  v_salary NUMBER;  -- Add missing declaration
BEGIN
  -- Rest of code
END;

ORA-00942: Table does not exist

-- Grant privileges on referenced table
GRANT SELECT ON hr.employees TO sales;

-- Or create synonym
CREATE SYNONYM emp FOR hr.employees;

PLS-00103: Syntax error

-- Review syntax at specified line/position
-- Common issues: missing semicolon, incorrect keywords

Automated Error Checking

-- Create monitoring procedure
CREATE OR REPLACE PROCEDURE check_compilation_errors AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM dba_errors;

  IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_count || ' compilation errors found');

    FOR rec IN (
      SELECT owner, name, type, COUNT(*) err_count
      FROM dba_errors
      GROUP BY owner, name, type
      ORDER BY err_count DESC
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(rec.owner || '.' || rec.name ||
                          ' (' || rec.type || '): ' || rec.err_count || ' errors');
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('No compilation errors found');
  END IF;
END;
/

Related Scripts

• Invalid Objects (../administration/dinvalid.sql) - Invalid object overview
• Object Dependencies (dobject.sql) - Object relationship analysis
• Source Code Analysis (dsource.sql) - PL/SQL source code examination
• Object Compilation (../generators/compile_invalid.md) - Mass compilation utilities

Best Practices

Development Workflow

1. Regular error checking: Run during development cycles
2. Dependency awareness: Compile in correct order
3. Error logging: Track and categorize common errors
4. Testing: Validate fixes in development environment

Production Deployment

1. Pre-deployment validation: Check for errors before release
2. Post-deployment verification: Confirm successful compilation
3. Rollback preparation: Have backups ready
4. Monitoring: Set up alerts for new compilation errors