Skip to Main Content
  • Questions
  • How to populate a table with random data

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ammar.

Asked: December 20, 2010 - 12:15 pm UTC

Last updated: December 21, 2010 - 6:54 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Dear tom,

I have a table with 5 columns and the first columns is sequenced:

create table customers
(
cust_id int primary key,
f_name varchar2(40),
l_name varchar2(40),
address varchar2(40),
postnr varchar2(40)
);
create sequence cust_id
minvalue 0
maxvalue 99999
start with 1
increment by 1
cache 20;


The question is:

How to populate that table randomly to generate a data of 10,000 rows?

Thanks in advance.



and Tom said...

You can modify gen_data to your needs. You would look for the primary key column and use your sequence.nextval instead of dbms_random.value - leaving the rest of the columns defaulting to the dbms_random values.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Problem when populating a nested table

Ammar, December 20, 2010 - 5:14 pm UTC

Thanks for replying.
I am going to simplify my previous table in order to post my followup:
create table customers
(
cust_id int primary key,
f_name varchar2(40)
);

To generate data, I was able to do the following:

INSERT INTO customers
SELECT  cust_id.nextval,
dbms_random.string('U',trunc(dbms_random.value(1,10)))
FROM  dual
CONNECT BY level <= 10000;


I have another table which is giving me an error when I try to populate it using the code above because it is nested with my first table:
create table orders
(
orders_id int primary key,
cust_id int references customer(cust_id)
);

and when populating it:
INSERT INTO orders
SELECT orders_id.nextval,
dbms_random.string('U',trunc(dbms_random.value(1,15)))
FROM  dual
CONNECT BY level <= 12;

I got the following error:
Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number".

What is the best way to solve this problem?
Thanks






Tom Kyte
December 21, 2010 - 6:54 am UTC

umm, you are inserting dbms_random.string('U',trunc(dbms_random.value(1,15))) into cust_id which is an int.

that - and I don't see a nested table anywhere in the example, but that is ok because the error you are getting is simply because you are inserting a string into an int.

More to Explore

DBMS_RANDOM

More on PL/SQL routine DBMS_RANDOM here