The 'EXCEPTION' block in the PL/SQL block is to catch a error raise during the execution of the program ('RUN TIME'). But not the 'COMPLIATION ERRORS'.
In your first experiment it was a run time error raised while program execution. But the "PL/SQL: ORA-00942: table or view does not exist" is compilation error raised as part of the Parsing (syntax and semantic checks) the SQL statements in the pl/sql block.
1 person found this helpful
ERROR at line 3:
ORA-06550: line 3, column 34:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 2:
PL/SQL: SQL Statement ignored
This is a compilation error.
Still your code has not been executed.
During the compilation if found the Table does not exist
Thanks guys for answer, it does make sense to me. this works too:
var v_b number
var v_dummy varchar2(1)
select dummy into :v_dummy from dual where 1=0;
when others then :v_b := 7;
If you are using shell script then you can use
WHENEVER SQLERROR EXIT 7
lets say you create the script
sqlplus -s scott/tiger << eof
whenever sqlerror exit 7;
select 1 from xxxx;--table not exist
echo Exited with $?
run the script
select 1 from xxxx
ERROR at line 1:
ORA-00942: table or view does not exist
Exited with 7