ORA-6504 Type of result set variables does not match

Question

I am trying to print records that are mismatching (by some criteria) in my two tables that I have queried below in my procedure

    CREATE OR REPLACE PROCEDURE one_two_mismatch( p_rc OUT SYS_REFCURSOR, 
    p_rc2 OUT SYS_REFCURSOR )
    AS
    BEGIN
    dbms_output.put_line('T1 Table'); 
    OPEN p_rc 
    FOR select t1.ENTITY_KEY, t1.ENTITY_ID, t1.COMPONENT_ID, t1.PARENT_KEY, 
    t1.ENTITY_TYPE_KEY from entity t1,(select entity_id  from (select 
    c.entity_id, count(e.component_id) as ONE_CNT, count(c.component_id) as 
    TWO_CNT from entity e, entity_cmm c where e.entity_id = 
    c.entity_id group 
    by c.entity_id) where ONE_CNT <> TWO_CNT) t2 where t1.ENTITY_ID = 
    t2.ENTITY_ID;

    dbms_output.put_line('T2 Table'); 
    OPEN p_rc2 
    FOR select t3.ENTITY_KEY, t3.ENTITY_ID, t3.COMPONENT_ID, t3.PARENT_KEY, 
    t3.ENTITY_TYPE_KEY from entity_cmm t3,(select entity_id  from 
    (select c.entity_id, count(e.component_id) as ONE_CNT, 
    count(c.component_id) as TWO_CNT from est_entity e, entity_cmm c 
    where e.entity_id = c.entity_id group by c.entity_id) where ONE_CNT <> 
    TWO_CNT) 
    t4 where t3.ENTITY_ID = t4.ENTITY_ID;
    END one_two_mismatch;

I have written the following statements below in my PL/SQL developer SQL window to execute the above procedure but am encountering an error in line 9 which says type of result set variable or query doesn't match

    declare
    p_rc sys_refcursor;
    p_rc2 sys_refcursor;
    l_rec est_entity%rowtype;
    m_rec est_entity_cmm%rowtype;
    begin
      one_two_MISMATCH(p_rc, p_rc2);
    LOOP
       FETCH p_rc INTO l_rec;
       EXIT WHEN p_rc%NOTFOUND;
       DBMS_OUTPUT.put_line(l_rec.ENTITY_KEY || ',' || l_rec.ENTITY_ID || 
       ',' || l_rec.COMPONENT_ID ||',' || l_rec.PARENT_KEY ||','|| 
       l_rec.ENTITY_TYPE_KEY );
    END LOOP;

  CLOSE p_rc;

 LOOP
 FETCH p_rc2 INTO m_rec;
 EXIT WHEN p_rc2%NOTFOUND;
 DBMS_OUTPUT.put_line( m_rec.ENTITY_KEY || ',' || m_rec.ENTITY_ID || ',' || 
 m_rec.COMPONENT_ID ||',' || m_rec.PARENT_KEY ||','|| 
 m_rec.ENTITY_TYPE_KEY);
 END LOOP;

  CLOSE p_rc2;
end;

Can someone please help?

Since we can't see your line numbers, please be more specific about your error. — Difster, Jul 31 '17 at 00:32
It gives an error in these lines : `l_rec est_entity%rowtype;` `m_rec est_entity_cmm%rowtype;` — Priya Thakur, Jul 31 '17 at 01:08
Are you sure that you are retrieving all columns from `est_entity` table in your cursor? — Incognito, Jul 31 '17 at 01:16

score 1 · Answer 1 · answered Jul 31 '17 at 02:32

Based on the information provided, it sounds like one or both queries in procedure cmm_st_mismatch do not have the correct column list. In the anonymous block, the two variables declared to receive the ref cursor records

l_rec est_entity%rowtype;
m_rec est_entity_cmm%rowtype;

are declared as row types. The ref cursor being fetched from must have the same number, type and order of columns as the table referenced in the rowtype variable declaration. Judging from the error you described, there is a mismatch.

ORA-6504 Type of result set variables does not match

1 Answers1