Oracle ORA-06550 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06550: PL/SQL Compilation Error — Causes, Fixes & Prevention

ORA-06550 is a PL/SQL compilation error that Oracle raises when it encounters problems while compiling a PL/SQL block, stored procedure, function, trigger, or package. It never appears alone — it always accompanies a more specific PLS-00xxx error message that pinpoints the exact line and column of the problem. Understanding how to read these companion messages is the fastest path to resolving this error.

Top 3 Causes

1. Undeclared Variable or Invalid Identifier (PLS-00201)

This is the most common cause. It occurs when you reference a variable that hasn't been declared, or when you misspell a table name, column name, or procedure name.

-- BAD: Variable v_count not declared
CREATE OR REPLACE PROCEDURE bad_proc AS
BEGIN
    v_count := 100; -- ORA-06550 / PLS-00201: identifier 'V_COUNT' must be declared
    DBMS_OUTPUT.PUT_LINE(v_count);
END;
/

-- GOOD: Declare variable properly in the declaration section
CREATE OR REPLACE PROCEDURE good_proc AS
    v_count NUMBER := 0;
BEGIN
    v_count := 100;
    DBMS_OUTPUT.PUT_LINE('Count is: ' || v_count);
END;
/

Always query USER_ERRORS to get the exact error detail:

SELECT name, line, position, text
FROM   user_errors
WHERE  name = 'YOUR_OBJECT_NAME'
ORDER  BY sequence;

2. Syntax Error — Missing Keywords or Mismatched Blocks (PLS-00103)

Missing END IF, END LOOP, or END keywords, misplaced semicolons, or wrong use of PL/SQL reserved words are classic triggers for this error.

-- BAD: Missing END IF
CREATE OR REPLACE PROCEDURE syntax_error_proc(p_val IN NUMBER) AS
BEGIN
    IF p_val > 100 THEN
        DBMS_OUTPUT.PUT_LINE('Greater than 100');
    -- END IF is missing here!
END;
/

-- GOOD: Properly closed IF block
CREATE OR REPLACE PROCEDURE syntax_ok_proc(p_val IN NUMBER) AS
BEGIN
    IF p_val > 100 THEN
        DBMS_OUTPUT.PUT_LINE('Greater than 100');
    ELSE
        DBMS_OUTPUT.PUT_LINE('100 or less');
    END IF;  -- Required!
END;
/

3. Package Spec / Body Mismatch or INVALID Object References

When a table or view referenced by a stored object is dropped or altered, that object becomes INVALID. Also, if your package body doesn't exactly match the signatures declared in the package specification, ORA-06550 is raised on compilation.

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

-- Recompile a single procedure
ALTER PROCEDURE your_proc_name COMPILE;

-- Recompile a package (spec first, then body)
ALTER PACKAGE your_pkg_name COMPILE SPECIFICATION;
ALTER PACKAGE your_pkg_name COMPILE BODY;

-- Recompile all INVALID objects in a schema at once
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'YOUR_SCHEMA', compile_all => FALSE);

Package spec/body mismatch fix:

-- SPEC declares NUMBER parameter
CREATE OR REPLACE PACKAGE emp_pkg AS
    PROCEDURE get_name(p_id IN NUMBER, p_name OUT VARCHAR2);
END emp_pkg;
/

-- BODY must match EXACTLY — same parameter names, types, and order
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
    PROCEDURE get_name(p_id IN NUMBER, p_name OUT VARCHAR2) AS
    BEGIN
        SELECT ename INTO p_name
        FROM   emp
        WHERE  empno = p_id;
    END get_name;
END emp_pkg;
/

Quick Fix Checklist

Run SELECT * FROM user_errors WHERE name = 'OBJECT_NAME'; immediately to get line/column details.
Check that all variables are declared in the DECLARE section before use.
Ensure all IF, LOOP, and BEGIN blocks have matching END IF, END LOOP, and END keywords.
Verify referenced tables, views, and synonyms exist and are accessible.
After any DDL changes, recompile dependent objects using DBMS_UTILITY.COMPILE_SCHEMA.

Prevention Tips

Use a proper IDE: Tools like SQL Developer or Toad provide real-time syntax highlighting and pre-compilation checks that catch ORA-06550 errors before you even submit the code.

Enforce coding standards: Adopt naming conventions (v_ for variables, p_ for parameters), consistent indentation, and mandatory peer code reviews before any deployment. Run DBMS_UTILITY.COMPILE_SCHEMA as part of every release pipeline to catch INVALID objects early.

Related Errors

Error Code	Description
PLS-00201	Identifier must be declared
PLS-00103	Encountered unexpected symbol
ORA-04068	Existing state of packages discarded
ORA-04067	Stored procedure has errors
ORA-00942	Table or view does not exist

📖 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