I would like to ask is it possible to catch syntax errors on exception block ? Example I've below exception in my code, when it hit exception it would notify me via email. I know syntax errors is during compilation, so it won't execute exception block, but is it possible to catch it ?
EXCEPTION
WHEN OTHERS
THEN
SEND EMAIL
The procedure wont be compiled if there are syntax error. in order to catch exception the Procedure should be compiled, if there are errors then you have to fix them. However you can create a procedure that run your procedure(as a string, dynamically), if your second procedure contain error , even syntax error , you can catch it and insert it into table.
Check this link for more info
other way you can also write a program which can check invalid objects and send mail to you with object names. here is sql to check invalid objects.
select * from all_objects where object_type in
('PACKAGE','PACKAGE BODY', 'PROCEDURE','FUNCTION') and status ='INVALID'
for syntax error you need to use tool like toad, pl/sql developer, sql developer etc. to show errors.
