OraFAQ Forum: SQL & PL/SQL » How to generate test data in PLSQL

Home » SQL & PL/SQL » SQL & PL/SQL » How to generate test data in PLSQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to generate test data in PLSQL [message #38873]

Thu, 23 May 2002 07:33

Ezz
Messages: 3
Registered: March 2002

Junior Member

Hi,
I need to be able to generate a schema full of test data. The schema itself has 32 tables, which are all inter-related using foreignkey constraints.

I need around 1.2 million rows in some tables, the other averaging in the high 1000s.

Is there an easy way to do this using PLSQL or do I need to purchase a data generation tool?

Thanks
E

Report message to a moderator

Re: How to generate test data in PLSQL [message #38878 is a reply to message #38873]

Thu, 23 May 2002 08:18

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

it depends on the complexity of data you want.
for a simple database i may use something like this.
here, i have created 10 sample data(rows), for table test_table1

SQL> create table test_table1
2 (id number,
3 name varchar2(20));

Table created.

SQL> create sequence test_id start with 1 increment by 1;

Sequence created.

SQL> create sequence test_name start with 1 increment by 1;

Sequence created.

SQL> ed
Wrote file afiedt.buf

1 begin
2 for mag in 1..10 loop
3 insert into test_table1 values (test_id.nextval,'Name'||test_name.nextval);
4 end loop;
5* end ;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from test_table1;

ID NAME
---------- --------------------
1 Name1
2 Name2
3 Name3
4 Name4
5 Name5
6 Name6
7 Name7
8 Name8
9 Name9
10 Name10

10 rows selected.

SQL>

Report message to a moderator

Re: How to generate test data in PLSQL [message #38891 is a reply to message #38878]

Fri, 24 May 2002 00:59

Ezz
Messages: 3
Registered: March 2002

Junior Member

Thanks Mahesh, unfourtanately, I need to be able to generate the keys between all 32 tables so it's fairly complex. Added to the complexity is the fact that some of the tables are used to tell our VB app which controls to display on the screen at run time, so the data has to be exact.

Report message to a moderator

Re: How to generate test data in PLSQL [message #38907 is a reply to message #38891]

Fri, 24 May 2002 17:56

andrew again
Messages: 2577
Registered: March 2000

Senior Member

If you have complex associations, then you need to write your own api (package/procs/triggers) to maintain that.

See this useful link. Note that the code won't work if your number columns don't have a size specified. i.e. use number(10)or whatever rather than just number.

Also - beware or creating test data which is just incremements rather than random values (except maybe for PK's - it affects the index size and performance).

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2151576678914

Report message to a moderator

Previous Topic:	How to return values from PL/SQL to UNIX shell variables?
Next Topic:	Compilation based on dependency

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 06:37:48 CDT 2024