Thanks for the question, pawan.

Asked: May 03, 2016 - 4:16 am UTC

Last updated: May 04, 2016 - 1:11 am UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

Dear Tom,

how to create dynamic table in oracle with dynamic column name and dynamic data type.

and Connor said...

You can do this with dynamic SQL, but I stress...that's generally not how Oracle was designed to work. You define structures up front, and use them. Creating tables on the fly generally ends up being trouble.

SQL> create or replace
  2  procedure make_a_table(p_table_name varchar2, p_column_name varchar2, p_data_type varchar2) is
  3  begin
  4    execute immediate
  5      'create table '||p_table_name||' ( '||p_column_name||' '||p_data_type||')';
  6  end;
  7  /

Procedure created.

SQL>
SQL> exec make_a_table('DEMO','DDD','DATE');

PL/SQL procedure successfully completed.

SQL>
SQL> desc demo
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ---------------------------
 DDD                                                                        DATE

But I stress ... this is not a path you want to be heading down

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

pawan kumar, May 03, 2016 - 7:02 am UTC

i want to create more than 100 tables at a time whose table name,column name and datatype stored in another table with different names.

pawan kumar, May 03, 2016 - 7:07 am UTC

please use the below sample data.
create table test (TABLE_NAME varchar2(40), COLUMN_NAME varchar2(40), ORACLE_DATATYPE varchar2(30), COLUMN_LENGTH number(20));
----
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('acceptadj','stocode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('acceptadj','smh','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('acceptadj','hoa','varchar2',22);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('acceptadj','transid','varchar2',10);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','otranstype','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','nddocode','varchar2',11);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','oformno','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','ntransid','varchar2',10);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','ntransidslno','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','otransidslno','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','earndedncode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','oservicemajor','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','oddocode','varchar2',11);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','ohoa','varchar2',22);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','nformno','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','oearndedncode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','nservicemajor','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','audempcode','varchar2',16);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','ntranstype','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','nstocode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','ostocode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','nhoa','varchar2',22);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromto','otransid','varchar2',10);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','nservicemajor','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','oempcode','varchar2',7);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','empcode','varchar2',7);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','oservicemajor','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','nddocode','varchar2',11);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','earndedncode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','ntransidslno','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','empslno','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','oformno','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','oearndedncode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','ostocode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','nstocode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','audempcode','varchar2',16);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','otranstype','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','otransidslno','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','ntranstype','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','ohoa','varchar2',22);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('amfromtoemp','nformno','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('ap_iss1','hoa','varchar2',22);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('ap_iss1','sdh','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('ap_iss1','dh','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('ap_iss1','ddocode','varchar2',11);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('ap_iss1','apformno','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('ap_iss1','formno','varchar2',3);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('ap_iss1','transid','varchar2',10);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('ap_iss1','stocode','varchar2',4);
Insert into EXPORT_TABLE (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH) values ('ap_iss1','aptransid','varchar2',10);

May 03, 2016 - 10:05 am UTC

See the follow up below for one method.

I agree with Connor though - generally creating tables on the fly is a bad idea. When using table data like this, there's always the chance someone will change your data. Then you'll end up with something you didn't want...

Chris

Muhammad Riaz, May 03, 2016 - 7:50 am UTC

@Pawan,

How about below simple PL/SQL block:


declare
 ddl_string varchar2(4000);
begin
 for x in (SELECT a.*, row_number() over (partition by table_name order by table_name) R_no FROM Export_table a order by table_name)
loop
 IF x.r_No = 1 THEN
  ddl_string := 'create table '||x.table_name||'('||x.column_name||' '||x.oracle_datatype||'('||x.column_length||'))';
 ELSE ddl_string :='alter table '||x.table_name||' add ('||x.column_name||' '||x.oracle_datatype||'('||x.column_length||'))';
 END IF;
-- dbms_output.put_line(ddl_string);
 execute immediate ddl_string;
end loop;
end;
/

riaz@ora11g> select object_name from user_objects where trunc(created) = trunc(sysdate);

OBJECT_NAME
----------------------------------------------------------------------------------------------------
AP_ISS1
AMFROMTOEMP
AMFROMTO
EXPORT_TABLE
ACCEPTADJ

riaz@ora11g> desc ap_iss1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STOCODE                          VARCHAR2(4)
 TRANSID                          VARCHAR2(10)
 FORMNO                           VARCHAR2(3)
 APFORMNO                         VARCHAR2(3)
 APTRANSID                        VARCHAR2(10)
 DH                               VARCHAR2(3)
 SDH                              VARCHAR2(3)
 HOA                              VARCHAR2(22)
 DDOCODE                          VARCHAR2(11)

May 03, 2016 - 10:07 am UTC

Yes, that's one way you could do it.

To: Muhammad Riaz

Rajeshwaran Jeyabal, May 03, 2016 - 12:34 pm UTC

Can you spell this?

S*Q*L*I*N*J*E*C*T*I*O*N

this could happen to the above code.

rajesh@ORA11G>
rajesh@ORA11G> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

rajesh@ORA11G> create or replace function foo
  2  return number as
  3     pragma autonomous_transaction;
  4  begin
  5     execute immediate ' drop table dept purge';
  6     commit;
  7     return 0 ;
  8  end;
  9  /

Function created.

rajesh@ORA11G> set feedback off
rajesh@ORA11G> drop table test purge;
rajesh@ORA11G> create table test (TABLE_NAME varchar2(40), COLUMN_NAME varchar2(40),
  2                     ORACLE_DATATYPE varchar2(30), COLUMN_LENGTH number(20));
rajesh@ORA11G> Insert into test (TABLE_NAME,COLUMN_NAME,ORACLE_DATATYPE,COLUMN_LENGTH)
  2             values ('T as select foo x from dual -- ','stocode','varchar2',4);
rajesh@ORA11G> commit;
rajesh@ORA11G> set feedback on
rajesh@ORA11G>
rajesh@ORA11G> declare
  2   ddl_string varchar2(4000);
  3  begin
  4   for x in (SELECT a.*, row_number() over (partition by table_name order by table_name) R_no FROM
  5     test a order by table_name)
  6  loop
  7   IF x.r_No = 1 THEN
  8    ddl_string := 'create table '||x.table_name||'('||x.column_name||' '||x.oracle_datatype||'('||x.column_length||'))';
  9     ELSE
 10    ddl_string :='alter table '||x.table_name||' add ('||x.column_name||' '||x.oracle_datatype||'('||x.column_length||'))';
 11     END IF;
 12   dbms_output.put_line(ddl_string);
 13   execute immediate ddl_string;
 14  end loop;
 15  end;
 16  /
create table T as select foo x from dual -- (stocode varchar2(4))

PL/SQL procedure successfully completed.

rajesh@ORA11G> select * from t;

         X
----------
         0

1 row selected.

rajesh@ORA11G> select * from dept;
select * from dept
              *
ERROR at line 1:
ORA-00942: table or view does not exist


rajesh@ORA11G>

Each and Every time, SQL is constructed dynamically (either in plsql or in the client side ) watch out for SQL-Injection.

May 03, 2016 - 3:14 pm UTC

Yep, another reason this is a bad idea.

agree

Ravi B, May 03, 2016 - 5:43 pm UTC

Totally agree with Rajeshwaran Jeyabal. I have seen similar implementation; one should be very careful about SQL injection whenever one smells "dynamic sql" . One more downside of this implementation is version control. How do you know what and when your metadata changed? Suppose you want to increase column length of one particular column in EXPORT_TABLE or change datatype. How do you know the history of your changes?

May 04, 2016 - 1:11 am UTC

Agree.

If this is a one-off activity to create some tables, fine. If it's part of the application itself....well....bad times ahead.

Muhammad Riaz, May 04, 2016 - 5:55 am UTC

I suppose this is one time activity (can't imagine creating tables on the fly frequently) so I think the use of dynamic SQL in this way should be OK.

In general, however, I would agree we should try avoiding using Dynamic SQL as much as we could and try binding the inputs to Dynamic SQL if that can not be avoided.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

To: Muhammad Riaz

agree

More to Explore

PL/SQL demos

PL/SQL docs