Home

Innovation

Articles

Products

Blog

Data Generation: Techniques in SQL

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.

by Philip Lambert

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
)