How to Generating Large Volumes of Random Records for a Table in Oracle? Options

codeling Posts: 1057 Points: 4443
Posted: Tuesday, January 19, 2016 2:37:28 PM

In Oracle, the built-in package DBMS_RANDOM is useful for generating random test data. The following example shows you how to generate large volumes of test records quickly by combining it into a query.

CREATE TABLE TestTable (
  id           NUMBER,
  number_col   NUMBER(5),
  string_col   VARCHAR2(50),
  created_date DATE,
  CONSTRAINT TestTable_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO TestTable
SELECT level AS id,
       TRUNC(DBMS_RANDOM.value(1,5)) AS number_col,
       DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,50))) AS string_col,
       TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) AS created_date
FROM   dual
CONNECT BY level <= 10000;

COMMIT;

The above INSERT statement will generate 10000 rows of random data, if you want more rows, just change 10000 to the number you want it to be.

 
Users browsing this topic
Guest