CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloperoracle18c
Fran Turkovic
Fran Turkovic

Reputation: 99

Why is code in PLSQL not executing after "exception" part?

So I made a bug in which I didn't put execute immediate inside nested begin-end block so my code didn't work. So basically I had

begin
 execute immediate 'select * from sales';
 exception when others then null;
 dbms_output.put_line(123);
end;

Dbms_output did not print "123" and I figured out I need to put execute immediate inside begin-end. I want to be sure, so my question is why is code not executing after exception part even though exception is not raised?

Upvotes: 1

Views: 1304

Answers (2)

0xdb
0xdb

Reputation: 3697

why is code not executing after exception part even though exception is not raised?

The reason is very simple - dbms_output.put_line in the question is not in "after exception part".

Properly indented code is equivalent to:

begin
    execute immediate 'select * from dual';
exception when others then 
    null;
    dbms_output.put_line(123);
end;
/

Exception block is everything between EXCEPTIONS and END; and will be executed if an exception occurs.

Upvotes: 2

Connor McDonald
Connor McDonald

Reputation: 11586

A couple of things in play here - parsing and execution.

If the table does not exist, then the statement cannot be parsed, and hence we'll get an exception immediately.

SQL> begin
  2     execute immediate 'select * from xxxx';
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 2

However, if the table DOES exist then the parse will be fine. Because you never specified an INTO that is all we do. We never needed to execute and commence the fetch phase and thus no error occurred. An easy to way to see that we never executed the statement is with something like

SQL> begin
  2     execute immediate 'select 1/count(*) from dual where 1=2';
  3  end;
  4  /

PL/SQL procedure successfully completed.

If we had executed, then we'd expect a divide by zero error to come out. The moment we see an INTO, then we'll need to fetch (which will require an execution).

Upvotes: 1

Related Questions