sql select into

Question

I have the below code in test.sql file. When there is a record in test_summary table everything works fine. But when there is no record inside the table it fails. I want it to continue to print the dbms_output message and process when there is no record. How can I do that?

declare

    total_var      number(20,2) := 0.0;
    nl_var          number(20,2) := 0.0;

begin

    select col1,col2
    into total_var,nl_var
    from testsch.test_summary;

    dbms_output.put_LINE('');
    dbms_output.put_LINE('testing1' || total_var);
    dbms_output.put_LINE('testing2' || nl_var);

end;

Gary Myers · Accepted Answer · 2010-07-21 01:27:33Z

8

I'd add a simple NO_DATA_FOUND exception handler.

declare
    total_var      number(20,2) := 0.0;
    nl_var          number(20,2) := 0.0;
begin
    begin
      select col1,col2
      into total_var,nl_var
      from testsch.test_summary;
    exception
      when no_data_found then null;
    end;
    dbms_output.put_LINE('');
    dbms_output.put_LINE('testing1' || total_var);
    dbms_output.put_LINE('testing2' || nl_var);
end;

answered Jul 21, 2010 at 1:27

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ThinkJet Over a year ago

Ok, but you must handle TOO_MANY_ROWS exception too, please add it to answer :)

Allan · Accepted Answer · 2010-07-21 12:16:24Z

0

In addition to Gary's perfectly valid answer, you can also avoid the error altogether by using an explicit cursor:

declare
    total_var      number(20,2) := 0.0;
    nl_var          number(20,2) := 0.0;
    cursor cur_test_summary is
      select col1,col2
      from testsch.test_summary;
begin
    open cur_test_summary;
    fetch cur_test_summary into total_var, nl_var;
    close cur_test_summary;
    dbms_output.put_LINE('');
    dbms_output.put_LINE('testing1' || total_var);
    dbms_output.put_LINE('testing2' || nl_var);
end;

answered Jul 21, 2010 at 12:16

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Comments

ThinkJet · Accepted Answer · 2010-07-21 13:56:10Z

0

I prefer variant with exception (see answer from @Gary), but there are another common variant, wich dials with problem:

declare

    total_var      number(20,2) := 0.0;
    nl_var          number(20,2) := 0.0;

begin

    select max(col1) , max(col2)
    into total_var,nl_var
    from testsch.test_summary;

    dbms_output.put_LINE('');
    dbms_output.put_LINE('testing1' || total_var);
    dbms_output.put_LINE('testing2' || nl_var);

end;

Your can use min() - don't matter. If no data found you got null values in variables

answered Jul 21, 2010 at 13:56

ThinkJet

6,76526 silver badges36 bronze badges

Comments

rfusca · Accepted Answer · 2010-07-21 18:24:52Z

0

Could do it all in one go. Avoid the not found and too many rows I believe.

select col1,col2 into total_var,nl_var
from
(
    select col1,col2 from 
    (
      select col1,col2
      from testsch.test_summary
      UNION ALL
      select null,null from dual
    ) t1 order by col1
) where rownum = 1

answered Jul 21, 2010 at 18:24

rfusca

7,7752 gold badges32 silver badges34 bronze badges

Collectives™ on Stack Overflow

sql select into

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related