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

User_35O2Q

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.

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

https://community.oracle.com/tech/developers/discussion/comment/16848156#Comment_16848156

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