CODEWITHSUNDEEP
arraysoracle-databaseplsql
AyanAgrawal
AyanAgrawal

Reputation: 1

Error in fetching a cursor of table of objects

I am new to learning PLSQL and definitely new to Objects Type (Abstract Data Types) in PLSQL. So anyway I saw a code in my company using a table operator while defining a cursor, so I thought of trying this. In the following Anonymous Block I get an error "PLS-00386: type mismatch found at 'V_CU' between FETCH cursor and INTO variables" but when I use c%rowtype (the commented part) instead of obj_emp while declaring V_CU I dont get that error. I can't understand the reason for this error.

Afterall a row of cursor c consist of a single element of object obj_emp doesn't it?

CREATE OR REPLACE TYPE obj_emp IS OBJECT (
    first_name  VARCHAR2(80),
    last_name   VARCHAR2(80),
    salary      NUMBER
);
/
CREATE OR REPLACE TYPE tab_emp_t IS TABLE OF obj_emp;
/

declare
    v_num number:=60;
    tab_emp tab_emp_t :=tab_emp_t();
    cursor c is select * from table(tab_emp);
    v_cu obj_emp --c%rowtype
    ;
      
begin

    for i in (select * from employees_copy where department_id = v_num) loop
        tab_emp.extend;
        tab_emp(tab_emp.last):=obj_emp(i.first_name, i.LAST_NAME, i.salary);
    end loop;
    
    open c;
    loop
        fetch c into v_cu;
        exit when c%notfound;
        dbms_output.put_line(v_cu.first_name||' '||v_cu.last_name||' earns '||v_cu.salary);
    end loop;
    close c;
end;
/

Upvotes: 0

Views: 438

Answers (1)

Littlefoot
Littlefoot

Reputation: 143013

Error is expected; it is fixed if you uncomment v_cu's datatype - it should still be c%rowtype.

But, what I think you wanted to try is displaying values stored into a collection. If that's so, have a look at the following code (slightly modified what you wrote; I don't have your tables so I used Scott's EMP instead).

SQL> declare
  2      v_num number:=10;
  3      tab_emp tab_emp_t :=tab_emp_t();
  4      v_cnt number:=1;
  5      cursor c is select * from table(tab_emp);
  6      v_cu c%rowtype; --obj_emp --c%rowtype
  7
  8  begin
  9      dbms_output.put_line('-------- Store values into a collection ---------');
 10      for i in (select * from emp where deptno = v_num) loop
 11          tab_emp.extend;
 12          tab_emp(v_cnt):=obj_emp(i.ename, i.job, i.sal);
 13          v_cnt:=v_cnt+1;
 14      end loop;
 15      v_cnt:=1;
 16
 17      dbms_output.put_line('-------- Display values from a cursor -----------');
 18      open c;
 19      loop
 20          fetch c into v_cu;
 21          exit when c%notfound;
 22          dbms_output.put_line(v_cu.first_name||' '||v_cu.last_name||' earns '||v_cu.salary);
 23      end loop;
 24
 25      dbms_output.put_line('-------- Display values from a collection -------');
 26
 27      for i in tab_emp.first .. tab_emp.last loop
 28        dbms_output.put_Line(tab_emp(i).first_name ||' '|| tab_emp(i).last_name ||' earns ' || tab_emp(i).salary);
 29      end loop;
 30  end;
 31  /

Results in

-------- Store values into a collection ---------
-------- Display values from a cursor -----------
CLARK MANAGER earns 2450
KING PRESIDENT earns 5000
MILLER CLERK earns 1300
-------- Display values from a collection -------
CLARK MANAGER earns 2450
KING PRESIDENT earns 5000
MILLER CLERK earns 1300

PL/SQL procedure successfully completed.

SQL>

Upvotes: 0

Related Questions