How to report an error from anonymous PL/SQL blocks
935884Jul 23 2012 — edited Jul 23 2012Hello,
The following SQL*Plus script
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT FAILURE
DECLARE
EXIST_INDEXES BOOLEAN := FALSE;
BEGIN
FOR INDEX IN (SELECT * FROM INDEXES)
LOOP
EXIST_INDEXES := TRUE;
DBMS_OUTPUT.PUT_LINE(INDEX.SCHEMA || '.' || INDEX.NAME);
END LOOP;
IF EXIST_INDEXES THEN
RAISE_APPLICATION_ERROR(-20000,'Before proceeding, it is recommended to drop the indexes listed above');
END IF;
END;
-- Here go SQL statements that should be executed if no indexes were found
produces this output when there is an entry in table/view INDEXES:
SCHEMA_1.INDEX_1
DECLARE
*
ERROR at line 1:
ORA-20000: Before proceeding, it is recommended to drop the indexes listed above
ORA-06512: at line 13
When there are entries in table/view INDEXES, how to:
- suppress the 'DECLARE' and '*' lines from appearing in the script output;
- skip executing the SQL statements after the PL/SQL block;
- have the script return a non-zero code?
Regards,
Angel Tsankov