Skip to Main Content
  • Questions
  • How to execute all SQL statements in procedure even though one of the select statement fails

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Nidhika.

Asked: January 22, 2020 - 3:45 pm UTC

Last updated: January 09, 2024 - 2:24 pm UTC

Version: Oracle 12

Viewed 1000+ times

You Asked

Hi Tom,

Is this possible to execute all select statement in procedure even though one of the select statement fails. I mean if one of the select statement fails only that
statement should not display output but rest of the statement in a procedure should work as expected if there is no error in rest of the statement.

CREATE OR REPLACE PROCEDURE bad_proc
IS
  x INT;
BEGIN
  SELECT 1 INTO x FROM dual;
  dbms_output.put_line(x);
  SELECT 2 INTO x FROM dual;
  dbms_output.put_line(x);
  SELECT 4/0 INTO x FROM dual;
  dbms_output.put_line(x);
  SELECT 5 INTO x FROM dual;
  dbms_output.put_line(x);
  SELECT 6 INTO x FROM dual;
  dbms_output.put_line(x);
EXCEPTION
WHEN zero_divide THEN
  dbms_output.put_line('HELP - I DIED HERE=>'||dbms_utility.format_error_backtrace );
END;
/
   
 set serveroutput on;
 exec BAD_PROC;


Actual OutPUT

PL/SQL procedure successfully completed.

1
2
HELP - I DIED HERE=>ORA-06512: at "HR.BAD_PROC", line 9


Expected Output :

PL/SQL procedure successfully completed.

1
2
HELP - I DIED HERE=>ORA-06512: at "HR.BAD_PROC", line 9

5
6


Regards,
N

and Chris said...

Wrap each SQL statement in its own begin ... exception ... end block:

CREATE OR REPLACE PROCEDURE bad_proc
IS
x INT;
BEGIN
  begin 
    SELECT 1 INTO x FROM dual;
    dbms_output.put_line(x);
  EXCEPTION
    WHEN zero_divide THEN
    dbms_output.put_line('HELP - I DIED HERE=>'||dbms_utility.format_error_backtrace );
  end;
  begin
    SELECT 2 INTO x FROM dual;
    dbms_output.put_line(x);
    EXCEPTION
    WHEN zero_divide THEN
    dbms_output.put_line('HELP - I DIED HERE=>'||dbms_utility.format_error_backtrace );
  end;
  begin
    SELECT 4/0 INTO x FROM dual;
    dbms_output.put_line(x);
    EXCEPTION
    WHEN zero_divide THEN
    dbms_output.put_line('HELP - I DIED HERE=>'||dbms_utility.format_error_backtrace );
  end;
  begin
    SELECT 5 INTO x FROM dual;
    dbms_output.put_line(x);
    EXCEPTION
    WHEN zero_divide THEN
    dbms_output.put_line('HELP - I DIED HERE=>'||dbms_utility.format_error_backtrace );
  end;
  begin  
    SELECT 6 INTO x FROM dual;
    dbms_output.put_line(x);
    EXCEPTION
    WHEN zero_divide THEN
    dbms_output.put_line('HELP - I DIED HERE=>'||dbms_utility.format_error_backtrace );
  end;
END;
/

set serveroutput on;
exec BAD_PROC;

1
2
HELP - I DIED HERE=>ORA-06512: at "CHRIS.BAD_PROC", line 20

5
6


PL/SQL procedure successfully completed.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Is it same behavior in the latest oracle versions as well

Rami Reddy V, December 20, 2023 - 2:40 pm UTC

Hi Tom,

Will it be same behavior in the latest oracle versions after Oracle 12c?

Thanks,
VRREDDY
Chris Saxon
January 09, 2024 - 2:24 pm UTC

Yes

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library