Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to use cursor in collections , because it is asking me to fetch more number of rows

User_35O2QSep 23 2022

create or replace type fn_typ is table of number;
/
declare
v_department_id fn_typ:=fn_typ(90);
v_first_name varchar2(100);
begin
for i in v_department_id.first..v_department_id.count
loop
select first_name into v_first_name from employees
where department_id =v_department_id(i);
dbms_output.put_line(v_first_name);
end loop;
end;
/
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 9

it is throwing me error like this,kindly clarify my doubt.

Comments

BluShadow

It's not asking you to fetch more rows, it's telling you that your select (of first_name) is returning more than 1 row for the specified department, yet it's expecting just a single value to be returned to populate your v_first_name variable (it can't populate the variable with multiple values at the same time).
You probably want something more like... (you obviously haven't posted all your code as we don't see how v_department_id is populated from your fn_typ function, and therefore I can't test this..)

declare
  v_department_id fn_typ:=fn_typ(90);
  v_first_name varchar2(100);
  cursor cEmployees(dept_id in number) is
    select first_name
    from   employees
    where  department_id = cEmployees.dept_id;
begin
  for i in v_department_id.first..v_department_id.count
  loop
    open cEmployees(v_department_id(i));
    loop
      fetch cEmployees into v_first_name;
      exit when cEmployees%notfound;
      dbms_output.put_line(v_first_name);
    end loop;
    close cEmployees;
  end loop;
end;

Note, this is considered a slow way to loop through records one by one.
You could also have a single query that bulk collects the names into a collection type and then loop through the collection to 'display' then (I use 'display' in the loose sense as dbms_output is not an appropriate method for displaying data)
i.e. something like... (again untested as I don't have your tables/data)

declare
  v_department_id fn_typ:=fn_typ(90);
  v_first_name sys.odcivarchar2list;
begin
  for i in v_department_id.first..v_department_id.count
  loop
    select first_name
    bulk collect into v_first_name
    from   employees
    where  department_id = v_department_id(i);
    for e in v_first_name.first..v_first_name.count
    loop
      dbms_output.put_line(v_first_name(e));
    end loop;
  end loop;
end;
User_3ABCE
select first_name from employees where department_id member of fn_typ(90);
BluShadow

That's not going to help the OP select the first_name into his variable.

1 - 3

Post Details

Added on Sep 23 2022
3 comments
87 views