Oracle ORA-06504 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06504: PL/SQL Return Types of Result Set Variables Do Not Match

ORA-06504 occurs in Oracle PL/SQL when a strongly typed REF CURSOR variable is assigned or fetched using a query whose column structure — including data types, number of columns, or column order — does not match the declared return type of the cursor. This error is commonly encountered when passing REF CURSORs between procedures or packages, or when using dynamic SQL with a typed cursor variable.

Top 3 Causes and SQL Examples

Cause 1: Strong REF CURSOR Type Mismatch

Declaring a strongly typed REF CURSOR and opening it with an incompatible query is the most common cause.

-- BAD: Opening a cursor with a mismatched table
DECLARE
    TYPE emp_cur_type IS REF CURSOR RETURN employees%ROWTYPE;
    v_cur emp_cur_type;
BEGIN
    -- This causes ORA-06504 because departments != employees%ROWTYPE
    OPEN v_cur FOR SELECT * FROM departments;
END;
/

-- GOOD: Query matches the declared return type
DECLARE
    TYPE emp_cur_type IS REF CURSOR RETURN employees%ROWTYPE;
    v_cur emp_cur_type;
    v_row employees%ROWTYPE;
BEGIN
    OPEN v_cur FOR SELECT * FROM employees;
    FETCH v_cur INTO v_row;
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_row.first_name);
    CLOSE v_cur;
END;
/

Cause 2: Mismatched Cursor Types Across Packages

When passing a REF CURSOR as an OUT parameter between packages, both sides must reference the same type definition. Defining separate but structurally similar cursor types in different packages will still trigger ORA-06504.

-- GOOD: Define a shared cursor type in a common package
CREATE OR REPLACE PACKAGE common_pkg AS
    TYPE emp_ref_cur IS REF CURSOR RETURN employees%ROWTYPE;
END common_pkg;
/

-- Producer package uses the shared type
CREATE OR REPLACE PACKAGE BODY producer_pkg AS
    PROCEDURE get_emp_cursor(p_cur OUT common_pkg.emp_ref_cur) IS
    BEGIN
        OPEN p_cur FOR SELECT * FROM employees;
    END;
END producer_pkg;
/

-- Consumer package also references the SAME shared type
DECLARE
    v_cur common_pkg.emp_ref_cur;
    v_row employees%ROWTYPE;
BEGIN
    producer_pkg.get_emp_cursor(v_cur);
    FETCH v_cur INTO v_row;
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_row.last_name);
    CLOSE v_cur;
END;
/

Cause 3: Dynamic SQL with a Strongly Typed Cursor

Using OPEN FOR with a dynamic SQL string against a strongly typed cursor can cause ORA-06504 at runtime when the query result doesn't match the declared type.

-- BAD: Dynamic SQL with a strongly typed cursor
DECLARE
    TYPE emp_cur_type IS REF CURSOR RETURN employees%ROWTYPE;
    v_cur emp_cur_type;
    v_sql VARCHAR2(500);
BEGIN
    v_sql := 'SELECT * FROM departments'; -- Runtime mismatch!
    OPEN v_cur FOR v_sql; -- ORA-06504
END;
/

-- GOOD: Use SYS_REFCURSOR (weak type) for dynamic SQL
CREATE OR REPLACE PROCEDURE fetch_any_table(
    p_table  IN  VARCHAR2,
    p_cursor OUT SYS_REFCURSOR
) AS
    v_sql VARCHAR2(500);
BEGIN
    -- Validate table name before use
    v_sql := 'SELECT * FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table);
    OPEN p_cursor FOR v_sql;
END;
/

-- Test
DECLARE
    v_cur SYS_REFCURSOR;
BEGIN
    fetch_any_table('EMPLOYEES', v_cur);
    -- Process results...
    CLOSE v_cur;
END;
/

Quick Fix Solutions

Scenario	Fix
Static query, strong type mismatch	Align the query columns with the declared `RETURN` type
Cross-package cursor passing	Centralize cursor type in a shared package
Dynamic SQL causing mismatch	Switch to `SYS_REFCURSOR` (weak type)

Prevention Tips

1. Centralize REF CURSOR type definitions.
Always declare shared cursor types in a single common package (e.g., common_types_pkg). Never redeclare the same logical cursor type in multiple packages — even if the structure looks identical, Oracle treats them as different types and will raise ORA-06504.

2. Use SYS_REFCURSOR for dynamic SQL scenarios.
If your query structure is determined at runtime, always prefer the weakly typed SYS_REFCURSOR over a strongly typed REF CURSOR. Reserve strongly typed cursors only for well-defined, static queries where compile-time type safety is genuinely needed. Pair this with proper unit tests that validate cursor compatibility before deployment.

📖 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