web analytics

Creating Table dynamically in Oracle Procedure

How can we create a table inside an Oracle procedure.

"CREATE TABLE tableName AS". We cannot execute this statement in procedures because upon creation of procedure it would give syntax error.

We can implement this with the help of EXECUTE IMMEDIATE command and then dynamically create table.

create or replace procedure Proc1 as
begin
execute immediate ‘create table NewTable(column1 integer )’;
end ;

One thought on “Creating Table dynamically in Oracle Procedure

  • January 23, 2012 at 11:02 pm
    Permalink

    Hi,

    I want to rename and recreate a table as given below, the code is working fine.
    But i want one modifications as- IF THE TABLE (M_BYP_20120123) ALREADY EXIST THEN STOP THERE AND EXIT.

    create or replace
    procedure prc_drp_crt is
    begin
    Execute immediate ‘rename ‘|| ‘M_BYP’ ||’ to’||’ M_BYP_’||to_char(sysdate-1,’YYYYMMDD’);
    Execute immediate ‘create table ‘||’M_BYP ‘||’as ‘|| ‘select * from M_BYP_’||to_char(sysdate-1,’YYYYMMDD’)|| ‘ where 1=2’;
    end prc_drp_crt;

Leave a Reply

%d bloggers like this: