Couple of months back, I was asked to run a script on UAT as part of a change deployment. When I looked into it, I got shocked. You can see yourself why..
REM
REM NAME
REM abc.sql - Delete Contact
REM
REM ********************************************************
PROMPT *** abc.sql ... ***
set define off
set serveroutput on
delete from XXXXXXXX where EMAIL='abc.xys@oracle.com';
dbms_output.put_line('*** Script Confirmation : Property Deleted from XXXXXXXX ***');
commit;
exception
when others then
dbms_output.put_line('*** Error: sqlcode: ' || sqlcode || '; sqlerrm: ' || substr(sqlerrm, 1, 100) || ' ***');
PROMPT *** abc.sql ... completed ***
REM ********************************************************
EXIT
If you notice, there is no DECLARE block, no BEGIN block, only exception block!!!!
I asked the developer how it didn’t give errors on development environment? He told “yes, but the record got deleted and commit was done. That’s all I want. It served the purpose.”
He was one among those who attended training on PL/SQL earlier that month. Needless to say, he has learnt the “Error handling” very well, at least.