how to create dynamic table in oracle with dynamic column name and dynamic data type with out any views or any other table type

Question

Thanks for all,We can create a table dynamically with the help of execute immediate query. But when we create a table it is been created but if i wanted to create table dynamically with dynamic no of columns then question was raised. Actually I had created a table but when I created no of columns along with the table then a lots of errors raised. The following is the code which I had written in the Oracle in a procedure.

declare
    no_of_cols number:=&no_of_cols;
    colname varchar2(20);
    coldata varchar2(20);
    i number;
begin
    execute immediate 'create table smap1(nam varchar2(10))';
    age:='age';
    datf:='number'
    if(no_of_cols>=2) then
        for i in 2..no_of_cols loop
            colname:=age;
            coldata:=datf;
            execute immediate 'alter table smapl add '||colname||' '||coldata;  
        end loop;
    end if;
end;

then this code executing with four columns with same type if no_of_cols is 5.Then i had modified the code and run the plsql program.the program is as follows

declare
no_of_cols number:=&no_of_cols;
colname varchar2(20);
age varchar2(20);
datf varchar2(20);
coldata varchar2(20);
i number;
begin
    execute immediate 'create table smap1(nam varchar2(10))';
    if(no_of_cols>=2) then
        for i in 2..no_of_cols loop
            age :=&age;
            datf:=&datf;
            colname:=age;
            coldata:=datf;
            execute immediate 'alter table smapl add '||colname||' '||coldata;  
        end loop;
    end if;
end;

The following are the errors which are generated when the above procedure is created

  [Error] Execution (13: 19): ORA-06550: line 13, column 19:
  PLS-00103: Encountered the symbol ";" when expecting one of the following:

  ( - + case mod new not null <an identifier>
  <a double-quoted delimited-identifier> <a bind variable>
  continue avg count current exists max min prior sql stddev
  sum variance execute forall merge time timestamp interval
  date <a string literal with character set specification>
  <a number> <a single-quoted SQL string> pipe
  <an alternatively-quoted string literal with character set specification>
  <an alternatively

i had done some modifications for the above plsql,then the plsql code will as follows

declare
no_of_cols number:=&no_of_cols;
colname varchar2(20):='&colname';
coldata varchar2(20):='&coldata';
i number;
begin
 execute immediate 'create table smap1(nam varchar2(10))';
if(no_of_cols>=2) then

    for i in 2..no_of_cols loop
       execute immediate 'alter table smapl add '||colname||' '||coldata;  
    end loop;
end if;
end;

then after executing i am getting the following error and it doenot read column name dynamically

[Error] Execution (1: 1): ORA-02263: need to specify the datatype for this column
 ORA-06512: at line 10

You are mixing plsql variables and sql*plus variables (&). Plus, you cannot create an empty table and add columns to it - you need to concatenate strings into a single create table (...) string and only then execute immediate. — haki
– haki, Commented Jul 23, 2014 at 6:49
"a lots of errors raised." What were the errorz that were raised? — sampathsris
– sampathsris, Commented Jul 23, 2014 at 6:54
i forgot to mention those errors now i had edited the question — Smart003
– Smart003, Commented Jul 23, 2014 at 6:56

sampathsris · Accepted Answer · 2014-07-23 07:11:16Z

3

You cannot use semicolons in `EXECUTE IMMEDIATE` for single statements

Here's a quote from the documentation:

Except for multi-row queries, the dynamic string can contain any SQL statement (without the final semicolon) or any PL/SQL block (with the final semicolon).

Remove the semicolon from EXECUTE IMMEDIATE.

execute immediate 'create table smap1(nam varchar2(10));'; -- this is your code
execute immediate 'create table smap1(nam varchar2(10))';  -- correct code, no semicolon at end

But there's another problem.

You need to understand how substitution variables (`&variable`) works

SQL*Plus will prompt for substituion variables only once: just before the script compiles, before running it. And then the variables are replaced in the script verbatim, after which it will get compiled and executed.

For example, when you run your script, SQL*Plus recognizes that there are two unknown literals (&colname and &coldata), and will prompt for you. If you supply the values 'age', and 'number' for them, SQL*Plus will rewrite the script like this:

declare
    -- omitted to add clarity
begin
    execute immediate 'create table smap1(nam varchar2(10));';
    if(no_of_cols>=2) then
        for i in 2..no_of_cols loop
            colname:=age;
            coldata:=number;
            execute immediate 'alter table smapl add '||colname||' '||coldata;  
        end loop;
    end if;
end;

So if you want to assign a string literal to a variable, and you want to get that string from a substitution variable, you need to do this:

colname varchar2(30) := '&colname'; -- notice the single quotes

Assuming you provided 'age' for colname SQL*Plus will happily convert this to:

colname varchar2(30) := 'age';

So, placing a substitution variable inside a loop will not make SQL*Plus repeatedly prompt you for it's value.

edited Jul 23, 2014 at 7:11

answered Jul 23, 2014 at 7:04

sampathsris

22.4k12 gold badges74 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Smart003 Over a year ago

thanks for the solution.i had removed the ; in execute immediate statement. but the problem is if the no_of_cols is 5 then i wanted five different columns but by using the above code i am getting all duplicate columns

sampathsris Over a year ago

Yes, the answer is that "placing a substitution variable inside a loop will not make SQL*Plus repeatedly prompt you for it's value." So you cannot do that. Will research a way to do it and get back to you.

Smart003 Over a year ago

thanks you, i had another error.It does not take column name while execution and it display error name [Error] Execution (1: 1): ORA-02263: need to specify the datatype for this column ORA-06512: at line 10

Smart003 Over a year ago

Actually i had a problem when i had created with another table then i got the error [Error] Execution (1: 1): ORA-02263: need to specify the datatype for this column ORA-06512: at line 10 and table or view does not exit

Wernfried Domscheit · Accepted Answer · 2014-07-23 08:48:08Z

0

For better understanding and analyze you should do it like this:

sqlcmd varhchar(30000);
begin
    sqlcmd := 'create table smap1(nam varchar2(10))';
    DBMS_OUTPUT.PUT_LINE(sqlcmd);
    execute immediate sqlcmd;
    if(no_of_cols>=2) then
        for i in 2..no_of_cols loop
            age :=&age;
            datf:=&datf;
            colname:=age;
            coldata:=datf;
            sqlcmd := 'alter table smapl add '||colname||' '||coldata;  
            DBMS_OUTPUT.PUT_LINE(sqlcmd);
            execute immediate sqlcmd;
        end loop;
    end if;

answered Jul 23, 2014 at 8:48

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

3 Comments

Smart003 Over a year ago

When i had executed in oracle 11g client in Toad, then i am getting an error it is ORA-06550: line 15, column 19:PLS-00103: Encountered the symbol ";" when expecting one of the following:< - + case mod new not null <an identifier> <a double-quoted delimited-identifier><a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number><a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specification><an alternatively

Wernfried Domscheit Over a year ago

What is the generated SQL Statement?

Smart003 Over a year ago

that statement is 'alter table <table-name> add <column-name> datatype'

Collectives™ on Stack Overflow

how to create dynamic table in oracle with dynamic column name and dynamic data type with out any views or any other table type

2 Answers 2

You cannot use semicolons in `EXECUTE IMMEDIATE` for single statements

You need to understand how substitution variables (`&variable`) works

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

You cannot use semicolons in EXECUTE IMMEDIATE for single statements

You need to understand how substitution variables (&variable) works

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

You cannot use semicolons in `EXECUTE IMMEDIATE` for single statements

You need to understand how substitution variables (`&variable`) works