Assert for SQL*Plus

Whenever you have to administer an Oracle database, you end up with a bunch of SQL*Plus scripts. Unfortunately, SQL*Plus is not a real scripting language and has therefore no control constructs at all - even a simple if-then-else is missing.

The only option you have is to use

whenever sqlerror exit failure

to exit whenever a SQL statement fails. This can help you to detect and avoid the duplicate use of initialization scripts with DDL statements. On the other hand, it will hardly help to stop the erroneous use of DML statements as they will not fail but simply write out "0 rows updated".

Shortly said, it would sometimes be very useful if you could at least check a simple condition and stop execution if the check fails - a feature widely known as Assert. Using the whenever sqlerror statement, I found a way to provide an Assert-like functionality in SQL*Plus.

Have a look at the script ASSERT.SQL below. It is called with a select statement as condition for the assertion. If the select statement returns any data, the calling program continues. If no data is returned, the assertion is considered as failed and the calling program is stopped.

Here is an example how to use it. Suppose you want to check for the existence of the table MY_TABLE before you execute some DML against it. Your script will look like this:

@assert 'select * from user_tables where table_name = ''MY_TABLE'''
-- your DML statement go here

If the table MY_TABLE does not exist, your program will get stopped with the following error message:

declare
*
FEHLER in Zeile 1:
ORA-20001: Assertion failed: select * from user_tables where table_name =
'MY_TABLE'
ORA-06512: at line 11

Enjoy,
Thomas

--
-- ASSERT.SQL
--
-- Provide Assert-like functionality for SQL*Plus.
--
-- Author:
-- Thomas Mauch
--
-- Usage:
-- Call this script with a select statement as condition for the
-- assertion. If the select statement returns any data, the calling
-- program continues. If no data is returned, the assertion is
-- considered as failed and the calling program is stopped.
--
-- Example:
-- @assert 'select * from myTable where myId = 1'
--
-- Note:
-- - This script sets verify and feedback and changes the behavior
--   in case of SQL errors which must be adapted to your needs.
-- - The use of the Q-quote-operator for the hassle-free use of
--   quoted strings requires Oracle 10g


-- Do not show substitution made
set verify off
-- Do not give feedback about executed commands
set feedback off

-- Stop script if an error occurs
whenever sqlerror exit failure

-- Implementation of the ASSERT functionality:
-- Evaluate the given query. If at least one row is selected, the
-- exists-clause return true and makes the select-into-statement
-- succeed. If no row is selected, it fails with a no data found
-- error.
declare
  n integer;
begin
  execute immediate
    'declare n integer; ' ||
    'begin ' ||
    q'#select 1 into n from dual where exists ( &1 ); #' ||
    'end;';
exception
  when no_data_found then
    raise_application_error(-20001, q'#Assertion failed: &1#');
  when others then
    raise_application_error(-20001, q'#Error in assertion: &1#', true);
end;
/

-- Reset error handling
whenever sqlerror exit continue