How do you create a table with random number of fields in Oracle using PL/SQL?

Question

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?

Answer 1

"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.

Answer 2

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.

Answer 3

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