August 11, 2007: On select into and the too_many_rows and no_data

What value has a variable into which I select (in PL/SQL) and if the select statement raises a too_many_rows or no_data_found exception?

It might be argued that the value of the variable either doesn't change or becomes null since there was an exception. It turns out, however, that neither is the case. As usual, this can be easily demonstrated.

create table tbl_sel_into (
  col_1    number,
  col_2    varchar2(10)
);

insert into tbl_sel_into values (1, 'one' );
insert into tbl_sel_into values (2, 'two' );
insert into tbl_sel_into values (4, 'four');
insert into tbl_sel_into values (4, 'vier');

The function that selects from the table. Note the ret := p2; that assigns the second argument to the variable ret which is the same variable into which the statement selects.

create function fct_sel_into (p1 in tbl_sel_into.col_1%type, 
                              p2 in varchar2               ) return varchar2 is
  ret varchar2(30);
begin

  ret := p2;

  begin

    select col_2 into ret 
      from tbl_sel_into
     where col_1 = p1;

  exception 
    when no_data_found then ret := 'no_data_found: ' || ret;          
    when too_many_rows then ret := 'too_many_rows: ' || ret;
  end;

  return ret;

end fct_sel_into;
/

set serveroutput on

begin
  dbms_output.put_line(fct_sel_into(1, '***'));
  dbms_output.put_line(fct_sel_into(2, '***'));
  dbms_output.put_line(fct_sel_into(3, '***'));
  dbms_output.put_line(fct_sel_into(4, '***'));
end;
/

As expected, the first and second call of the function return one and two, respectively, since 1 and 2 is found in the table exactly once and no exception is thrown.

However, if the no_data_found exception is thrown, the value of ret remains unchanged while in the case of a too_many_rows exception, ret is assigned the value of the first record that matches the where condition:

one
two
no_data_found: ***
too_many_rows: four

August 11, 2007: On select into and the too_many_rows and no_data_found exception

More on Oracle


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger