CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
JohnD
JohnD

Reputation: 353

How to return more than 1 record?

create or replace procedure p_inout
  (v_emp_lname in varchar2)
  as
  v_first_name varchar2(20);
begin
  select first_name into v_first_name
  from employees
  where last_name=v_emp_lname;
  dbms_output.put_line(v_first_name);
end p_inout;
/

The above code is used to return the first name when the person's last name was inputted. But there are many duplicate last names. And when i tried to run the query i got this error.

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "HR.P_INOUT", line 6
ORA-06512: at line 1
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

How do I return more than records?

Upvotes: 2

Views: 3699

Answers (1)

Tomasz Żuk
Tomasz Żuk

Reputation: 1288

You select into a scalar variable v_first_name which can, obviously, store only a single value, so selecting multiple rows doesn't make sense in that case. If there might be many records with the same last name, try something like this:

create or replace procedure p_inout
  (v_emp_lname in varchar2)
  as
begin
  for rec in
  (
    select first_name
    from employees
    where last_name=v_emp_lname
  )
  loop
    dbms_output.put_line(rec.first_name);
  end loop;
end p_inout;
/

Upvotes: 2

Related Questions