Forum Stats

  • 3,852,068 Users
  • 2,264,066 Discussions
  • 7,904,960 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: 10 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

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,128 Silver Trophy

    Why did you post this in an XML based forum? The question has nothing to do with XML at all. You are already aware of the SQL & PL/SQL forum (https://community.oracle.com/tech/developers/categories/sql_and_pl_sql) from other recent posts.

    This query

    select first_name 
      into v_first_name 
      from employees
     where department_id =v_department_id(i);
    

    Is returning two or more rows. You are trying to tell Oracle to place multiple values into a single variable. It can't, hence the "ORA-01422: exact fetch returns more than requested number of rows".

    Restart your question over on the correct forum and include details as to what you are trying to accomplish. This means what your expected output should be after a successful run.