Generate test data with Oracle simple query

The most easiest way to generate test data in the Oracle is the method of using CONNECT BY LEVEL with the package DBMS_RANDOM. With the query we can generate required amount of rows and with usage the package DBMS_RANDOM we add necessary filling of columns. The easiest query in this case will be following:

SELECT LEVEL AS ID,
       dbms_random.String('X', 10) AS NAME
FROM dual CONNECT BY LEVEL < 10;

Output 9 rows in 2 columns:

ID      NAME
--      --

1 4txer6oa9h
2 sppv4klnh1
3 oz33rs9fsy
4 0dl8azesaq
5 gc33xxnm2g
6 4i5hmvm6uj
7 5oovp3o4oe
8 a10k5lwrqt
9 9eyxgm98f5

Adjust a value of LEVEL to get a required amount of test data rows. In our case LEVEL 

also is a unique identifier and can be used as primary key instead of using 

sequence.

In order to get more columns we add more calls of methods of the DBMS_RANDOM
package. The most useful methods of the package for generate test data with 

example of usage you can find bellow:

SELECT dbms_random.value(),
       -- random number, great or equal 0 and less then 1

       dbms_random.value(1,5),
       -- random number, in the predefined range

       dbms_random.normal(),
       -- positive and negative random number

       dbms_random.random(),
       -- deprecated function, should not be used

       dbms_random.string('x',10),
       -- random string, with letters and digits

       trunc(SYSDATE,'yyyy') + dbms_random.value(1,360) 
       -- example for generate random date
FROM dual;

More detailed description for the package DBMS_RANDOM you can find by the following

link http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_random.htm.

Row to Column in Oracle

Row to Column in Oracle

The easiest solution for pivot the row with delimiter to column.

select

  regexp_substr('1,2,3,4,5','[^,]',level)
from dual
connect by regexp_substr('1,2,3,4,5','[^,]',1,level) is not null