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

The common shortcoming of Undo

Most of the applications today feature undo functionality for several or even an unlimited number of steps. But unfortunately, this functionality has a common shortcoming where it would be most crucial: when it comes to closing documents.

If the application asks you whether you want to save the changes you made, the following can easily happen: you promptly click discard because you're sure that you have not made any valuable changes or you start thinking what changes you might have made - and as the application wont't tell you easily what you have changed, you finally click discard anyhow.

Obviously this decision can be wrong. And if you notice it just a few seconds too late, your work may be gone - forever... Note that even automatically created backup file normally do not help any further as they typically have been deleted right now - or contain the document in the state when you loaded it and before you started editing.

So the question arises: Why is the undo history related to a specific document? Should there not be a concept like a "global" undo where you can play back your actions as you can on a VCR?

Clearly there are some subtle problems to solve and the "global" undo shold not replace but extend the document specific undo, but nevertheless it would add another net of safety for all users.