Wednesday, January 11, 2017

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

This problem took sometime to be resolved and I am sharing here the reason and solution


Problem :



I was getting this exception in my oracle code
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

Reason :


I was trying to load data in a sys ref cursor , and later in a loop I was trying to fetch this cursor in a row type. Declration for my row type was

v_student_row          Student_TBL%rowtype;

I was loading data into the cursor using below query

select t.name,t.roll_no,t.email_address from Student_TBL t

and later was


loop
        fetch v_student_cur
          into v_student_row;

where Student_TBL had below column

name
roll_no
email_address
address
height
weight
blood_group

When trying to fetch I was getting
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

The reason was I was not selecting all columns in my select query , so when trying to assign the data in my cursor to the row_type oracle was throwing above error.

Solution :



Once the problem was understood solution was very easy i.e. to select * from student table not few columns.