
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;
Solution:1
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;
Solution:2
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;
Solution:3
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
Solution:4
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
Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
EmoticonEmoticon