Forum Stats

  • 3,872,464 Users
  • 2,266,426 Discussions
  • 7,911,213 Comments

Discussions

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

User_35O2Q
User_35O2Q Member Posts: 18 Green Ribbon

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.

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,544 Red Diamond

    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
    User_3ABCE Member Posts: 175 Silver Badge
    select first_name from employees where department_id member of fn_typ(90);
    
  • BluShadow
    BluShadow Member, Moderator Posts: 42,544 Red Diamond