ORA-06504 is raised when the columns of a cursor's query do not match the variable or record it is fetched into. With a weakly typed cursor variable such as SYS_REFCURSOR, the compiler cannot check the query, so the mismatch shows up at run time, at the FETCH. The predefined exception is ROWTYPE_MISMATCH. Make the query's column list match the target, in number and in type.
Reproduce the Error
Example:
create table demo_fares (route_code varchar2(10) primary key, fare number);
insert into demo_fares values ('DEL-BOM', 120);
commit;
declare
c sys_refcursor;
r demo_fares%rowtype; -- two columns
begin
open c for select route_code from demo_fares; -- one column
fetch c into r;
close c;
end;
/Output:
Table DEMO_FARES created. 1 row inserted. Commit complete. declare * ERROR at line 1: ORA-06504: PL/SQL: Return types of Result Set variables or query do not match ORA-06512: at line 6
The record has two fields, ROUTE_CODE and FARE, but the query returns one column; line 6 is the FETCH.
Fix: Match the Columns to the Record
Example:
set serveroutput on declare c sys_refcursor; r demo_fares%rowtype; begin open c for select route_code, fare from demo_fares; -- the same columns as the record fetch c into r; close c; dbms_output.put_line(r.route_code || ' ' || r.fare); end; /
Output:
DEL-BOM 120 PL/SQL procedure successfully completed.
Things to Know
- A strongly typed REF CURSOR (RETURN demo_fares%ROWTYPE) lets the compiler check the query when the cursor is opened in the same unit.
- Columns are matched by position, not by name, so the order matters.
- Code that opens the cursor in one unit and fetches in another, such as a function returning SYS_REFCURSOR, is where this error most often appears.
Related Guides
Conclusion
ORA-06504 means a cursor's columns and the fetch target differ. Select the same columns, in the same order and of compatible types, as the record or variables, and use strongly typed cursors where the compiler can check them.
