CODEWITHSUNDEEP
oracle-databaseplsqlselect-intoora-01422
Rajiv Choudhary
Rajiv Choudhary

Reputation: 141

Why is Oracle SELECT INTO statement throwing error?

I write a code to create procedure in oracle, It's successful created but when RUN from sql developer to view output it's show error.

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TESTUSER.USER_FEEDBACK", line 5
ORA-06512: at line 2

code:

create or replace PROCEDURE user_feedback
IS
initiator VARCHAR2(50);
BEGIN
select first_name into initiator
from person_info;
END ;

Please suggest me.

Upvotes: 2

Views: 2138

Answers (2)

rsorero
rsorero

Reputation: 21

@Lalit Kumar B gave a good answer.

My addition - There are different ways to access multiple rows in PLSQL - generally speaking you can use an implicit or explicit cursors. The REFCURSORS mentioned above are variables that point to a cursor, so you can copy them between pieces of code.

Example for an implicit cursor (in your case) would be -

CREATE OR REPLACE PROCEDURE user_feedback AS
BEGIN
   for c_name in select first_name from person_info loop
      /* do what ever you like with c, e.g - */
      insert into other_table (name) values (c.first_name);
      dbms_output.put_line(c.first_name);
   end loop;
END;
/

An explicit cursor usage would look like this -

CREATE OR REPLACE PROCEDURE user_feedback AS
 cursor c_name is
  select first_name from person_info;
 l_name person_info.first_name%type;
BEGIN
 open c_name;
 while c_name%found loop
  fetch c_name into l_name;
  insert into other_table (name) values (c_name.first_name);
  dbms_output.put_line(c_name.first_name);
 end loop;
END;
/

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

ORA-01422: exact fetch returns more than requested number of rows

select first_name into initiator
from person_info;

The error message is pretty clear. Your SELECT statement above returns more than 1 row, however, you are trying to fetch multiple rows into scalar variable. You could use SELECT INTO only for a single row. For multiple rows, you need to use collections.

  • Either use a filter predicate to return only singe row
  • Or, use a collection to hold multiple rows.

For example, using the standard EMP table in SCOTT schema:

SQL> DECLARE
  2  v_empno NUMBER;
  3  BEGIN
  4  SELECT empno INTO v_empno FROM emp;
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

Let's add the filter WHERE ename = 'SCOTT' to return only a single row:

SQL> DECLARE
  2  v_empno NUMBER;
  3  BEGIN
  4  SELECT empno INTO v_empno FROM emp WHERE ename = 'SCOTT';
  5  END;
  6  /

PL/SQL procedure successfully completed.

Let's look at example of multiple rows using a REFCURSOR.

For example,

SQL> var r refcursor
SQL> begin
  2     open :r for select empno from emp;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print r

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.

Upvotes: 5

Related Questions