CODEWITHSUNDEEP
sqloracle-database
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Create table based on the data inserted in a table

I have a table which contains names of tables. Structure of all these tables is same which is also shown below. Whenever there is an additional row with new name of table in this table, I am to create a table with that name. I was looking for some way, that as soon as a new row with new name of table is inserted into following table, a new table with that name should be created automatically via some trigger or some other way.

ID  TableName
1   Electronics
2   Mechanical
3   Capacitor

Table Structure

CREATE TABLE Mechanical
(
ID INT,
ATTRIBUTE_1 VARCHAR2(25),
ATTRIBUTE_2 VARCHAR2(25),
ATTRIBUTE_3 VARCHAR2(25)
)

So as soon as we have a new row in the table with new table name, above table creation command should create a new table having the name of table inserted into the table. Hope this explains the requirement.

Upvotes: 0

Views: 255

Answers (1)

kfinity
kfinity

Reputation: 9091

Edit: As Alex Poole points out, you can do this with an autonomous transaction but it's not recommended since it will leave orphan tables with rollbacks.

CREATE OR REPLACE PROCEDURE create_table(table_name in varchar2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  execute immediate 'create table ' || table_name || ' (ID INT, ATTRIBUTE_1 VARCHAR2(25), ATTRIBUTE_2 VARCHAR2(25), ATTRIBUTE_3 VARCHAR2(25))';
END;
/
CREATE OR REPLACE TRIGGER create_table_trigger
AFTER INSERT ON my_table FOR EACH ROW 
BEGIN
    create_table(:new.tablename);
END;
/

An alternative might be to create a scheduled job which checks the table every minute or so and creates new tables for any rows which don't already have existing tables.

CREATE OR REPLACE PROCEDURE auto_create_tables
IS
BEGIN
  for t in (select tablename from my_table where tablename not in (select table_name from all_tables))
  loop
     execute immediate 'create table ' || t.tablename || ' (ID INT, ATTRIBUTE_1 VARCHAR2(25), ATTRIBUTE_2 VARCHAR2(25), ATTRIBUTE_3 VARCHAR2(25))';
  end loop;
END;
/
begin
    DBMS_SCHEDULER.CREATE_JOB (
         job_name             => 'auto_create_table_job',
         job_type             => 'STORED_PROCEDURE',
         job_action           => 'auto_create_tables',
         start_date           => sysdate,
         repeat_interval      => 'FREQ=MINUTELY;INTERVAL=1;',
         enabled              => TRUE);
end;
/

Upvotes: 2

Related Questions