Efficiently creating test data with Oracle

When working with an Oracle database, you may have the need to create some test data.
As DUAL contains just a single row, you often abuse ALL_OBJECTS as source for your statement.

But even ALL_OBJECTS has its limitation regarding the number of rows, so what worked for a few thousands rows will suddenly stop working for millions of rows - and then you have to switch to PL/SQL or to another programming language or your choice for this simple task.

But there is a solution in pure Oracle SQL by using hierarchical queries. Have a look at the following statement which helps you to generate as much rows as you want:

-- Using a hierarchical query to generates 1000 rows
select level id from dual
  connect by level <= 1000;

Note that level starts with 1, so the query above will create the numbers from 1 to 1000.

You can then you use CREATE TABLE AS or INSERT with a subquery to efficiently create your test data. The needed row data you can easily generate from the LEVEL value by using functions like MOD, DECODE or a CASE statement.

The following example creates a test data with 3 columns and 1000 rows:

create table t1 as
  select
    level id,
    mod(level, 10) count10,
    case when mod(level, 10) = 0 then
      '----'
    else
      lpad(level, 4, '-')
    end text
  from dual
  connect by level <= 1000;

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