Create tables and generate data dynamically using DYNAMIC SQL

This article will be helpful for multiple database users like Oracle / PostgreSQL / EPAS (EDB Postgres Advanced Server) users to CREATE random tables, INSERT random data into those tables, and DROP those tables at one go, using few stored procedures and Dynamic SQL within.

Oracle Database:

— Create N number of tables with X number of columns.

CREATE OR REPLACE PROCEDURE create_tbl_proc(v_tbl IN INT, v_cl IN INT)
AUTHID CURRENT_USER
AS
v_sql varchar(32767);
BEGIN
for t in 1..v_tbl loop
v_sql := ‘CREATE TABLE t’||t||'(‘;
for i in 1..v_cl loop
v_sql := v_sql || ‘c’||i||’ int’;
if i < v_cl then  v_sql := v_sql || ‘,’;
else v_sql := v_sql || ‘)’;
end if;
end loop;
EXECUTE IMMEDIATE v_sql;
end loop;
END;
/

Procedure created.

— Insert into N number of tables with X numbers of columns for Y numbers of rows.

CREATE OR REPLACE PROCEDURE insert_tbl_proc(v_tbl IN INT, v_cl IN INT, v_rc int)
AUTHID CURRENT_USER
AS
v_sql varchar2(32767);
begin
for t in 1..v_tbl loop
for i in 1..v_rc loop
v_sql := ‘INSERT INTO t’||t||’ values (‘;
  for j in 1..v_cl 
  loop
     v_sql := v_sql ||’round(DBMS_RANDOM.value(low => 1, high => 100))’;
     if j < v_cl then  v_sql := v_sql || ‘,’; 
     else v_sql := v_sql || ‘)’;
     end if;
  end loop;
  EXECUTE IMMEDIATE v_sql;
end loop;
end loop;
END;
/

Procedure created.

— Drop N number of tables.

CREATE OR REPLACE PROCEDURE drop_tbl_proc(v_tbl IN INT)
AUTHID CURRENT_USER
AS
v_sql varchar(32767);
begin
for i in 1..v_tbl loop
v_sql := ‘DROP TABLE t’||i;
EXECUTE IMMEDIATE v_sql;
end loop;
END drop_tbl_proc;
/

Procedure created.

SQL> exec create_tbl_proc(5,3);

PL/SQL procedure successfully completed.


SQL> SELECT * FROM tab WHERE TNAME LIKE ‘T_’;

TNAME TABTYPE  CLUSTERID
————— ——– ————–
T1 TABLE
T2 TABLE
T3 TABLE
T4 TABLE
T5 TABLE

SQL> desc t5
 Name Null?         Type
 ————– ————- ———————
 C1 NUMBER(38)
 C2 NUMBER(38)
 C3 NUMBER(38)


SQL> exec insert_tbl_proc(5,3,2);

PL/SQL procedure successfully completed.

SELECT * FROM t1;

C1   C2      C3
———- ———- ———-
99   13      56
78   99      84

SQL> SELECT * FROM t5;

C1   C2      C3
———- ———- ———-
29   98      81
58   28      44

SQL> exec drop_tbl_proc(5);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tab WHERE TNAME LIKE ‘T_’;

no rows selected

PostgreSQL Database:

— Create N number of tables with X number of columns.

CREATE OR REPLACE FUNCTION create_tbl_func(v_tbl IN INT, v_cl IN INT) RETURNS INT
AS
$$
DECLARE
v_sql varchar(32767);
BEGIN
for t in 1..v_tbl loop
v_sql := ‘CREATE TABLE t’||t||'(‘;
for i in 1..v_cl loop
v_sql := v_sql || ‘c’||i||’ int’;
if i < v_cl then  v_sql := v_sql || ‘,’;
else v_sql := v_sql || ‘)’;
end if;
end loop;
EXECUTE v_sql;
end loop;
RETURN 1;
END; $$ LANGUAGE PLPGSQL;

— Insert into N number of tables with X numbers of columns for Y numbers of rows.

CREATE OR REPLACE FUNCTION insert_tbl_func(v_tbl IN INT, v_cl IN INT, v_rc int) RETURNS INT 
AS
$$
DECLARE
v_sql text;
begin
for t in 1..v_tbl loop
for i in 1..v_rc loop
v_sql := ‘INSERT INTO t’||t||’ values (‘;
   for j in 1..v_cl 
   loop
      v_sql := v_sql ||round(random()*100); 
      if j < v_cl then  v_sql := v_sql || ‘,’; 
      else v_sql := v_sql || ‘)’;
      end if;
   end loop;
   EXECUTE  v_sql;
end loop;
end loop;
RETURN 2;
END; $$ LANGUAGE PLPGSQL;

— Drop N number of tables.

CREATE OR REPLACE FUNCTION drop_tbl_func(v_tbl IN INT) RETURNS INT
AS
$$
DECLARE
v_sql varchar(32767);
begin
v_sql := ‘DROP TABLE ‘;
for i in 1..v_tbl loop
v_sql := v_sql || ‘t’||i;
if i < v_tbl then  v_sql := v_sql || ‘,’;
else v_sql := v_sql || ‘, t’||v_tbl;
end if;
end loop;
EXECUTE v_sql;
RETURN 3;
END; $$ LANGUAGE PLPGSQL;

— Create 5 tables with 3 columns:
postgres=# select create_tbl_func(5,3);
 create_tbl_func 
—————–
               1
(1 row)

postgres=# d
        List of relations
 Schema | Name | Type  |  Owner  
——–+——+——-+———
 public | t1   | table | prabhat
 public | t2   | table | prabhat
 public | t3   | table | prabhat
 public | t4   | table | prabhat
 public | t5   | table | prabhat
(5 rows)

postgres=# d t5
                 Table “public.t5”
 Column |  Type   | Collation | Nullable | Default 
——–+———+———–+———-+———
 c1     | integer |           |          | 
 c2     | integer |           |          | 
 c3     | integer |           |          | 

— Insert 
postgres=# select insert_tbl_func(5,3,2);
 insert_tbl_func 
—————–
               2
(1 row)

postgres=# SELECT * FROM t1;
 c1 | c2 | c3 
—-+—-+—-
 60 | 35 | 17
 80 | 49 | 70
(2 rows)

postgres=# SELECT * FROM t5;
 c1 | c2 | c3 
—-+—-+—-
 79 | 91 | 51
 18 | 28 | 77
(2 rows)

— Drop all the 5 tables:
postgres=# select drop_tbl_func(5);
 drop_tbl_func 
—————
             3
(1 row)

postgres=# d
psql: error: Did not find any relations.

EPAS (EnterpriseDB Postgres Advanced Server)

— Create N number of tables with X number of columns.
CREATE OR REPLACE FUNCTION create_tbl_func(v_tbl IN INT, v_cl IN INT) RETURN NUMBER
AS
v_sql varchar(32767);
BEGIN
for t in 1..v_tbl loop
v_sql := ‘CREATE TABLE t’||t||'(‘;
for i in 1..v_cl loop
v_sql := v_sql || ‘c’||i||’ int’;
if i < v_cl then  v_sql := v_sql || ‘,’;
else v_sql := v_sql || ‘)’;
end if;
end loop;
EXECUTE IMMEDIATE v_sql;
end loop;
RETURN 1;
END;

— Insert into N number of tables with X numbers of columns for Y numbers of rows.

CREATE OR REPLACE FUNCTION insert_tbl_func(v_tbl IN INT, v_cl IN INT, v_rc int) RETURN NUMBER 
AS
v_sql varchar2(32767);
begin
for t in 1..v_tbl loop
for i in 1..v_rc loop
v_sql := ‘INSERT INTO t’||t||’ values (‘;
for j in 1..v_cl 
loop
v_sql := v_sql ||round(random()*100);
if j < v_cl then  v_sql := v_sql || ‘,’; 
else v_sql := v_sql || ‘)’;
end if;
end loop;
EXECUTE IMMEDIATE v_sql;
end loop;
end loop;
RETURN 2;
END;

— Drop N number of tables.

CREATE OR REPLACE FUNCTION drop_tbl_func(v_tbl IN INT) RETURN NUMBER
AS
v_sql varchar(32767);
begin
v_sql := ‘DROP TABLE ‘;
for i in 1..v_tbl loop
v_sql := v_sql || ‘t’||i;
if i < v_tbl then  v_sql := v_sql || ‘,’;
else v_sql := v_sql || ‘, t’||v_tbl;
end if;
end loop;
EXECUTE IMMEDIATE v_sql;
RETURN 3;
END;


— Create 5 tables with 3 columns:
edb=# select create_tbl_func(5,3);
 create_tbl_func 
—————–
               1
(1 row)

edb=# d
        List of relations
 Schema | Name | Type  |  Owner  
——–+——+——-+———
 public | t1   | table | prabhat
 public | t2   | table | prabhat
 public | t3   | table | prabhat
 public | t4   | table | prabhat
 public | t5   | table | prabhat
(5 rows)

edb=# d t5
                 Table “public.t5”
 Column |  Type   | Collation | Nullable | Default 
——–+———+———–+———-+———
 c1     | integer |           |          | 
 c2     | integer |           |          | 
 c3     | integer |           |          | 

— Insert 
edb=# select insert_tbl_func(5,3,2);
 insert_tbl_func 
—————–
               2
(1 row)

edb=# SELECT * FROM t1;
 c1 | c2 | c3 
—-+—-+—-
 23 | 49 | 66
 67 | 64 | 26
(2 rows)

edb=# SELECT * FROM t5;
 c1 | c2 | c3 
—-+—-+—-
 65 | 51 | 41
 45 | 78 | 19
(2 rows)

— Drop all the 5 tables:
edb=# select drop_tbl_func(5);
 drop_tbl_func 
—————
             3
(1 row)

edb=# d
psql: error: Did not find any relations.

2 thoughts on “Create tables and generate data dynamically using DYNAMIC SQL”

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn