Oracle ORA-06540 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06540: PL/SQL Compilation Error – Causes, Fixes & Prevention

ORA-06540 occurs when Oracle fails to compile a PL/SQL object such as a procedure, function, package, or trigger during execution or compilation. This error is rarely standalone—it almost always appears alongside ORA-06550 and a PLS-XXXXX error that pinpoints the actual problem. Understanding the root cause requires examining all error messages together, not just ORA-06540 in isolation.

Top 3 Causes

1. Referenced Objects in INVALID State

This is the most common cause in production environments. When a table, view, or other dependency is modified or dropped, Oracle automatically marks all dependent PL/SQL objects as INVALID. Calling an INVALID object triggers ORA-06540 when the automatic recompilation attempt fails.

-- Check for INVALID objects in your schema
SELECT object_name, object_type, status, last_ddl_time
FROM   user_objects
WHERE  status = 'INVALID'
ORDER  BY object_type, object_name;

-- Recompile a specific procedure
ALTER PROCEDURE my_procedure COMPILE;

-- Recompile an entire schema using UTL_RECOMP
EXEC UTL_RECOMP.RECOMP_SERIAL('MY_SCHEMA');

-- Or run Oracle's built-in recompile script as SYS
@?/rdbms/admin/utlrp.sql

2. Syntax Errors or Invalid Object References in PL/SQL Code

Typos in data types, references to non-existent columns, or undefined variables cause the compilation to fail silently during CREATE OR REPLACE. The object is saved as INVALID, and ORA-06540 fires the first time it is called.

-- Example of broken code (typo in data type)
CREATE OR REPLACE PROCEDURE broken_proc AS
  v_name VARCHARR(100);  -- typo: should be VARCHAR2
BEGIN
  SELECT ename INTO v_name FROM emp WHERE empno = 7369;
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

-- Check compilation errors immediately after creation
SHOW ERRORS PROCEDURE broken_proc;

-- Query errors table for details
SELECT line, position, text
FROM   user_errors
WHERE  name = 'BROKEN_PROC'
  AND  type = 'PROCEDURE'
ORDER  BY sequence;

-- Fixed version
CREATE OR REPLACE PROCEDURE broken_proc AS
  v_name VARCHAR2(100);  -- correct data type
BEGIN
  SELECT ename INTO v_name FROM emp WHERE empno = 7369;
  DBMS_OUTPUT.PUT_LINE(v_name);
END;
/

3. Insufficient Privileges (Missing Direct Grants)

PL/SQL objects compiled under Definer Rights (the default) require direct object privileges at compile time. Privileges granted through a Role are not recognized during PL/SQL compilation, which catches many developers off guard.

-- WRONG: Role-based grant does not help PL/SQL compilation
-- GRANT DBA TO my_user; -- roles are ignored during compilation

-- CORRECT: Grant privileges directly to the user
GRANT SELECT ON other_schema.employees TO my_user;
GRANT EXECUTE ON other_schema.util_package TO my_user;

-- Verify direct grants
SELECT owner, table_name, privilege
FROM   user_tab_privs
WHERE  grantee = 'MY_USER';

-- Alternative: Use Invoker Rights to bypass this at compile time
CREATE OR REPLACE PROCEDURE flexible_proc
  AUTHID CURRENT_USER
AS
BEGIN
  -- Runs with caller's privileges; no compile-time privilege check
  NULL;
END;
/

Quick Fix Checklist

Read all error messages – ORA-06540 is always accompanied by ORA-06550 and a PLS error. Focus on those.
Run SHOW ERRORS immediately after a failed CREATE OR REPLACE.
Recompile INVALID objects using ALTER ... COMPILE or utlrp.sql.
Check direct grants – replace role-based grants with direct GRANT statements.
Query user_errors for a full diagnostic picture.

-- Full diagnostic query
SELECT name, type, line, position, text
FROM   user_errors
ORDER  BY name, sequence;

Prevention Tips

Automate post-deployment validation. Add an INVALID object check as the final step of every deployment script. Fail the deployment if any INVALID objects remain.

-- Post-deployment validation gate
SELECT object_name, object_type
FROM   dba_objects
WHERE  owner  = 'MY_SCHEMA'
  AND  status = 'INVALID';
-- Expected result: 0 rows

Enable PL/SQL compile-time warnings in development. Catch potential issues before they reach production by turning on PLSQL_WARNINGS at the session level during development and code review.

-- Enable all compile warnings for the session
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';

-- Recompile and review warnings
ALTER PROCEDURE my_procedure COMPILE PLSQL_WARNINGS='ENABLE:ALL';

SELECT line, position, text, attribute
FROM   user_errors
WHERE  name      = 'MY_PROCEDURE'
  AND  attribute = 'WARNING';

Related Errors

Error Code	Description
ORA-06550	Always paired with ORA-06540; provides exact line/column of the error
PLS-00103	Unexpected token – usually a missing semicolon or wrong keyword
PLS-00201	Identifier not declared – undefined variable or missing privilege
ORA-04068	Package state discarded after recompile – affects existing sessions
ORA-04021	Timeout waiting to lock object for compilation

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community