ORA-06550 PL/SQL Compilation Error - Complete Resolution Guide

ORA-06550: Line N, Column N: PL/SQL Compilation Error

Error Overview

Error Text: ORA-06550: line N, column N: PLS-XXXXX: <error description>

The ORA-06550 error is a wrapper error that indicates a PL/SQL compilation failure. It always appears alongside a more specific PLS- error code that describes the actual problem. The line and column numbers help locate the exact position of the error in your code.

Understanding the Error Format

ORA-06550: line 5, column 10:
PLS-00201: identifier 'EMPLOYEE_TABLE' must be declared
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored

• Line number: The line in your PL/SQL block where the error occurred
• Column number: The position within that line
• PLS-XXXXX: The specific compilation error
• Description: Human-readable error message

Most Common PLS Errors

PLS Error	Description	Common Cause
PLS-00201	Identifier must be declared	Typo or missing variable/object
PLS-00103	Encountered symbol when expecting	Syntax error
PLS-00306	Wrong number/types of arguments	Procedure call mismatch
PLS-00302	Component must be declared	Package/type member missing
PLS-00330	Invalid use of type	Type conversion issue
PLS-00382	Expression is of wrong type	Type mismatch
PLS-00428	INTO clause expected	Missing INTO in SELECT

Error Examples and Solutions

Example 1: PLS-00201 - Identifier Not Declared

-- ERROR: Typo in table name
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employes;  -- Typo!
END;
/
-- ORA-06550: line 4, column 40:
-- PLS-00201: identifier 'EMPLOYES' must be declared

-- SOLUTION: Fix the typo
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;  -- Correct
END;
/

-- ERROR: Variable not declared
BEGIN
  v_name := 'John';  -- Variable never declared
END;
/
-- ORA-06550: line 2, column 3:
-- PLS-00201: identifier 'V_NAME' must be declared

-- SOLUTION: Declare the variable
DECLARE
  v_name VARCHAR2(100);
BEGIN
  v_name := 'John';
END;
/

Example 2: PLS-00103 - Syntax Error

-- ERROR: Missing semicolon
DECLARE
  v_count NUMBER
BEGIN
  v_count := 1;
END;
/
-- ORA-06550: line 3, column 1:
-- PLS-00103: Encountered the symbol "BEGIN" when expecting ;

-- SOLUTION: Add the semicolon
DECLARE
  v_count NUMBER;  -- Semicolon added
BEGIN
  v_count := 1;
END;
/

-- ERROR: Missing THEN in IF statement
BEGIN
  IF 1 = 1
    DBMS_OUTPUT.PUT_LINE('Equal');
  END IF;
END;
/
-- ORA-06550: line 3, column 5:
-- PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting THEN

-- SOLUTION: Add THEN
BEGIN
  IF 1 = 1 THEN
    DBMS_OUTPUT.PUT_LINE('Equal');
  END IF;
END;
/

-- ERROR: Missing END for loop
BEGIN
  FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  -- Missing END LOOP;
END;
/
-- PLS-00103: Encountered the symbol "END" when expecting LOOP

-- SOLUTION: Add END LOOP
BEGIN
  FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
/

Example 3: PLS-00306 - Wrong Arguments

-- ERROR: Wrong number of parameters
CREATE OR REPLACE PROCEDURE greet(p_name VARCHAR2, p_age NUMBER) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello ' || p_name);
END;
/

BEGIN
  greet('John');  -- Missing second parameter
END;
/
-- ORA-06550: line 2, column 3:
-- PLS-00306: wrong number or types of arguments in call to 'GREET'

-- SOLUTION 1: Provide all parameters
BEGIN
  greet('John', 30);
END;
/

-- SOLUTION 2: Use default value in procedure
CREATE OR REPLACE PROCEDURE greet(
  p_name VARCHAR2,
  p_age NUMBER DEFAULT 0
) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello ' || p_name);
END;
/

Example 4: PLS-00428 - Missing INTO Clause

-- ERROR: SELECT without INTO in PL/SQL
BEGIN
  SELECT first_name, last_name
  FROM employees
  WHERE employee_id = 100;
END;
/
-- ORA-06550: line 2, column 3:
-- PLS-00428: an INTO clause is expected in this SELECT statement

-- SOLUTION: Add INTO clause
DECLARE
  v_first VARCHAR2(50);
  v_last VARCHAR2(50);
BEGIN
  SELECT first_name, last_name
  INTO v_first, v_last
  FROM employees
  WHERE employee_id = 100;
END;
/

-- Or use a cursor for multiple rows
BEGIN
  FOR rec IN (SELECT first_name, last_name FROM employees) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.first_name || ' ' || rec.last_name);
  END LOOP;
END;
/

Example 5: PLS-00302 - Package Component Not Found

-- ERROR: Calling non-existent package procedure
BEGIN
  DBMS_OUPUT.PUT_LINE('Hello');  -- Typo in package name
END;
/
-- ORA-06550: line 2, column 3:
-- PLS-00302: component 'PUT_LINE' must be declared

-- SOLUTION: Fix the package name
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');  -- Correct spelling
END;
/

Example 6: PLS-00382 - Wrong Expression Type

-- ERROR: Assigning wrong type
DECLARE
  v_date DATE;
BEGIN
  v_date := 'not a date';  -- String can't convert to date
END;
/
-- ORA-06550: line 4, column 14:
-- PLS-00382: expression is of wrong type

-- SOLUTION: Use proper date format
DECLARE
  v_date DATE;
BEGIN
  v_date := TO_DATE('2024-01-15', 'YYYY-MM-DD');
END;
/

Diagnostic Approach

Step 1: Read the Error Carefully

ORA-06550: line 7, column 15:
PLS-00201: identifier 'DEPTNO' must be declared
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored

1. Go to line 7 in your code
2. Look at column 15 for the problem
3. The identifier DEPTNO is not recognized
4. The SQL statement was ignored due to the error

Step 2: Check for Common Issues

-- Common syntax checklist:
-- ✓ All statements end with semicolons
-- ✓ BEGIN has matching END
-- ✓ LOOP has END LOOP
-- ✓ IF has THEN and END IF
-- ✓ CASE has END CASE (or END in searched CASE)
-- ✓ Variables declared before use
-- ✓ Table/column names spelled correctly
-- ✓ SELECT has INTO clause in PL/SQL

Step 3: Check Object Existence

-- Verify table exists
SELECT table_name FROM all_tables
WHERE table_name = UPPER('your_table_name');

-- Verify column exists
SELECT column_name FROM all_tab_columns
WHERE table_name = UPPER('your_table')
  AND column_name = UPPER('your_column');

-- Verify procedure/function exists
SELECT object_name, object_type FROM all_objects
WHERE object_name = UPPER('your_procedure');

-- Verify package procedure
SELECT procedure_name FROM all_procedures
WHERE object_name = UPPER('package_name');

Step 4: Check for Stored Program Errors

-- View compilation errors for stored objects
SELECT line, position, text
FROM all_errors
WHERE owner = 'YOUR_SCHEMA'
  AND name = 'YOUR_PROCEDURE'
ORDER BY sequence;

-- Or use SHOW ERRORS after CREATE
CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
  NULL
END;
/
SHOW ERRORS;

Common Patterns

Pattern 1: Block Structure Template

DECLARE
  -- Variable declarations (optional section)
  v_variable datatype;
BEGIN
  -- Executable statements (required)
  NULL;  -- Placeholder for empty block
EXCEPTION
  -- Exception handlers (optional section)
  WHEN OTHERS THEN
    NULL;
END;
/  -- Forward slash executes in SQL*Plus

Pattern 2: Proper IF-THEN-ELSE

BEGIN
  IF condition1 THEN
    -- statements
  ELSIF condition2 THEN  -- Note: ELSIF not ELSEIF
    -- statements
  ELSE
    -- statements
  END IF;  -- Required END IF
END;
/

Pattern 3: Proper Loop Structures

-- FOR loop
BEGIN
  FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;  -- Required END LOOP
END;
/

-- WHILE loop
DECLARE
  v_counter NUMBER := 1;
BEGIN
  WHILE v_counter <= 10 LOOP
    DBMS_OUTPUT.PUT_LINE(v_counter);
    v_counter := v_counter + 1;
  END LOOP;
END;
/

-- Basic loop with EXIT
BEGIN
  LOOP
    EXIT WHEN condition;  -- Must have exit condition
    -- statements
  END LOOP;
END;
/

Pattern 4: CASE Statement

-- Simple CASE
DECLARE
  v_grade CHAR(1) := 'B';
  v_result VARCHAR2(20);
BEGIN
  v_result := CASE v_grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    WHEN 'C' THEN 'Fair'
    ELSE 'Unknown'
  END;  -- Note: just END, not END CASE for expression
END;
/

-- Searched CASE statement
BEGIN
  CASE
    WHEN score >= 90 THEN grade := 'A';
    WHEN score >= 80 THEN grade := 'B';
    ELSE grade := 'C';
  END CASE;  -- END CASE for statement
END;
/

Prevention Strategies

1. Use an IDE with Syntax Checking

• SQL Developer highlights errors before execution
• PL/SQL Developer provides real-time validation
• VS Code with Oracle extensions

2. Follow Coding Standards

-- Good practices that prevent errors:

-- 1. Consistent indentation
-- 2. One statement per line
-- 3. Always use END labels for readability
CREATE OR REPLACE PROCEDURE my_proc IS
BEGIN
  FOR emp_rec IN (SELECT * FROM employees) LOOP
    IF emp_rec.salary > 10000 THEN
      -- process
      NULL;
    END IF;
  END LOOP;  -- emp_rec loop
END my_proc;  -- Labels help match BEGIN/END
/

-- 3. Explicit variable typing
DECLARE
  v_emp_name employees.last_name%TYPE;  -- Uses column type
  v_count PLS_INTEGER := 0;             -- Specific type

3. Test in Stages

-- Build complex code incrementally
-- Step 1: Test the query
SELECT * FROM employees WHERE department_id = 10;

-- Step 2: Add PL/SQL wrapper
BEGIN
  FOR rec IN (SELECT * FROM employees WHERE department_id = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.last_name);
  END LOOP;
END;
/

-- Step 3: Add error handling
-- Step 4: Create as stored procedure

Quick Reference: Required Keywords

Structure	Required Keywords
Block	BEGIN … END;
IF	IF … THEN … END IF;
LOOP	LOOP … END LOOP;
FOR	FOR … LOOP … END LOOP;
WHILE	WHILE … LOOP … END LOOP;
CASE expr	CASE … END
CASE stmt	CASE … END CASE;
Procedure	CREATE PROCEDURE … IS BEGIN … END;
Function	CREATE FUNCTION … RETURN type IS BEGIN … RETURN …; END;

Related Errors

• ORA-06502 - PL/SQL: numeric or value error (runtime)
• ORA-06512 - At line (runtime error stack)
• ORA-00942 - Table or view does not exist
• ORA-01403 - No data found

Summary

1. Read the full error - Line number, column, and PLS code
2. Check spelling - Table names, column names, variables
3. Verify syntax - Semicolons, THEN, END clauses
4. Declare variables - All variables must be in DECLARE section
5. Use INTO - SELECT statements in PL/SQL need INTO
6. Match structures - Every BEGIN needs END, every LOOP needs END LOOP
7. Use SHOW ERRORS - For stored program compilation issues