Software applications need to be tested and this may mean being dependent on a lot more data than can be entered through natural means. The quality and authenticity of generated data relies on the sophistication and effort put into the routines. This article describes some techniques to ease the coding.

Generating Rows

A useful technique with SQL for generating data is using the hierarchical join syntax CONNECT BY. The following statement generates ten rows numbered 1 throught to 10:

SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 10

Random Ordering

The dbms_random package is very useful for generating random numbers in a particular range, which has its uses in procedural data generation routines. Picking a single row or even a set of rows randomly from a table or result set of a query would normally be quite tricky, but there is a very useful and not so obvious use of the dbms_random package.

To randomly sort a result set of a query:

SELECT *
FROM (
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 10
)
ORDER BY dbms_random.value

To randomly select a single row from a query:

SELECT *
FROM
(
SELECT *
FROM
(
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 10
)
ORDER BY dbms_random.value
)
WHERE ROWNUM = 1

To randomly select a set of rows from a query:

SELECT *
FROM
(
SELECT *
FROM (
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 10
)
ORDER BY dbms_random.value
)
WHERE ROWNUM < 6

Analytic Functions

Analytic functions are a very powerful feature of Oracle SQL and can be very useful for generating test data. A couple of examples are listed below:

Cumulative Total

SELECT ROWNUM seq,
amount,
SUM(amount)OVER(ORDER BY ROWNUM) cumulative_amount
FROM
(
SELECT amount
FROM (
SELECT LEVEL amount
FROM dual
CONNECT BY LEVEL <= 10
)
ORDER BY dbms_random.value
)

Incrementals

SELECT ROWNUM seq,
amount,
LAG(amount,1)OVER(ORDER BY ROWNUM) last_amount,
amount - LAG(amount,1)OVER(ORDER BY ROWNUM) change
FROM
(
SELECT amount
FROM
(
SELECT LEVEL amount
FROM dual
CONNECT BY LEVEL <= 10
)
ORDER BY dbms_random.value
)