I need to create a Oracle tables with random number of columns for load testing. I just want to specify number of columns with type NUMBER, number of columns with type VARCHAR2 etc and the fields should be generated automatically. Also, I will be filling up the tables with random data for which I will be using dbms_random.
How can I achieve this?
You may generate such table by yourself.
Create table with required datatypes:
Create Table RandomTable
AS
Select dbms_random.string('A', 1) CharField,
dbms_random.string('a', 20) VarCharField,
TRUNC(dbms_random.value(0, 35000)) IntField,
dbms_random.value(0, 35000) NumberField,
Level SequenceField,
sysdate + dbms_random.value(-3500, 3500) DateField
from dual connect by level < 1000
(You can change 1000 to required rows numbers, and add required data types)
After that you can create or populate tables with random data from RandomTable
Create Table TestTable1
AS
SELECT CharField as a, NumberField as b
from RandomTable
INSERT INTO TestTable2(DateFrom, Quantity)
SELECT DateField, IntField
from RandomTable
Where SequenceField <= 500
Two approaches
1) Write code to generate text files containing the CREATE TABLE commands that you need to run and populate your tables, then execute these using SQL*Plus.
2) Use Dynamic SQL embedded inside PL/SQL -
PROCEDURE create_random_table
(pTableName IN VARCHAR2,
pNumberOfColumns IN INTEGER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
lCommand VARCHAR2(32000);
BEGIN
lCommand :=
'CREATE TABLE '||pTableName||'(';
FOR i IN 1..pNumberOfColumns LOOP
append your column definition here
END LOOP;
lCommand := lCommand||';';
--
EXECUTE IMMEDIATE lCommand;
END;
You could also use 'CREATE TABLE AS SELECT' to populate your table at the same time (see other answer).
This should give you a good starting point - there isn't anything in the system that will do what you want without writing code.
"I just want to specify number of columns with type NUMBER, number of columns with type VARCHAR2 etc and the fields should be generated automatically."
The following procedure does just that. Note that it is rather basic; you might want to make it more sophisticated, for example by varying the length of the varchar2 columns:
SQL> create or replace procedure bld_table
2 ( p_tab_name in varchar2
3 , no_of_num_cols in pls_integer
4 , no_of_var_cols in pls_integer
5 , no_of_date_cols in pls_integer
6 )
7 as
8 begin
9 execute immediate 'create table '||p_tab_name||' ('
10 ||' pk_col number not null'
11 ||', constraint '||p_tab_name||'_pk primary key (pk_col) using index)';
12 << numcols >>
13 for i in 1..no_of_num_cols loop
14 execute immediate 'alter table '||p_tab_name||' add '
15 ||' col_n'||trim(to_char(i))||' number';
16 end loop numcols;
17 << varcols >>
18 for i in 1..no_of_var_cols loop
19 execute immediate 'alter table '||p_tab_name||' add '
20 ||' col_v'||trim(to_char(i))||' varchar2(30)';
21 end loop varcols;
22 << datcols >>
23 for i in 1..no_of_date_cols loop
24 execute immediate 'alter table '||p_tab_name||' add '
25 ||' col_d'||trim(to_char(i))||' date';
26 end loop datcols;
27 end bld_table;
28 /
Procedure created.
SQL>
Here it is in action:
SQL> exec bld_table ('T23', 2, 3, 0)
PL/SQL procedure successfully completed.
SQL> desc t23
Name Null? Type
----------------------------------------- -------- ----------------------------
PK_COL NOT NULL NUMBER
COL_N1 NUMBER
COL_N2 NUMBER
COL_V1 VARCHAR2(30 CHAR)
COL_V2 VARCHAR2(30 CHAR)
COL_V3 VARCHAR2(30 CHAR)
SQL>
We can also use dynamic SQL to populate the table with rows of random data.
SQL> create or replace procedure pop_table
2 ( p_tab_name in varchar2
3 , p_no_of_rows in pls_integer
4 )
5 as
6 stmt varchar2(32767);
7 begin
8 stmt := 'insert into '||p_tab_name
9 || ' select rownum ';
10 for r in ( select column_name
11 , data_type
12 , data_length
13 from user_tab_columns
14 where table_name = p_tab_name
15 and column_name != 'PK_COL' )
16 loop
17 case r.data_type
18 when 'VARCHAR2' then
19 stmt := stmt ||', dbms_random.string(''a'', '||r.data_length||')';
20 when 'NUMBER' then
21 stmt := stmt ||', dbms_random.value(0, 1000)';
22 when 'DATE' then
23 stmt := stmt ||', sysdate + dbms_random.value(-1000, 0)';
24 end case;
25 end loop;
26 stmt := stmt || ' from dual connect by level <= '||p_no_of_rows;
27 execute immediate stmt;
28 end pop_table;
29 /
Procedure created.
SQL>
Note that the primary key is populated with the ROWNUM so it will most likely fail if the table already contains rows.
SQL> exec pop_table('T23', 4)
PL/SQL procedure successfully completed.
SQL> select * from t23
2 /
PK_COL COL_N1 COL_N2 COL_V1 COL_V2 COL_V3
---------- ---------- ---------- ------------------------------ ----------------------------- ------------------------------
1 913.797432 934.265814 NUtxjLoRQMCTLNMPKVGbTZwJeYaqnXTkCcWu WFRSHjXdLfpgVYOjzrGrtUoX jIBSoYOhSdhRFeEeFlpAxoanPabvwK
2 346.879815 104.800387 NTkvIlKeJWybCTNEdvsqJOKyidNkjgngwRNN PPIOInbzInrsVTmFYcDvwygr RyKFoMoSiWTmjTqRBCqDxApIIrctPu
3 93.1220275 649.335267 NTUxzPRrKKfFncWaeuzuyWzapmzEGtAwpnjj jHILMWJlcMjnlboOQEIDFTBG JRozyOpWkfmrQJfbiiNaOnSXxIzuHk
4 806.709357 857.489387 ZwLLkyINrVeCkUpznVdTHTdHZnuFzfPJbxCB HnoaErdzIHXlddOPETzzkFQk dXWTTgDsIeasNHSPbAsDRIUEyPILDT
4 rows selected.
SQL>
Again, there are all sorts of ways to improve the sophistication of the data.
As an aside, using these sorts of data pools for load testing is not always a good idea. Performance problems are often caused by skews in the distribution of data values which you just aren't going to get with DBMS_RANDOM. This is particularly true of some date columns - e.g. START_DATE - which would tend to be clustered together in real life but the above procedure will not generate that pattern. Similarly maxing out the varchar2 columns will lead to tables which take up more storage than they wlll under real-life usage.
In short, randomly generated data is better than nothing but we need to understand its weaknesses.