1 2 Previous Next 16 Replies Latest reply: Dec 11, 2012 3:16 PM by JustinCave Go to original post RSS
      • 15. Re: how to generete execute immediate of stmt
        6363
        bart_k_pl wrote:
        i got clue that it should look more or less like this:

        stmt:='create table '||name||'(';
        Getting closer.

        See example of create table statement from previously posted manual.

        http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#i2062833

        Notice that it may include size of columns and that in some cases this is required.
        for x in (select * from all_tab_columns where table_name=name) loop

        stmt:=stmt||x.column_name||' '||x.data_type||','
        Other things you will have to deal with are commas at the and of all but the last column definition.
        end loop;
        stmt:=stmt||')';
        execute immediate stmt;
        You should use dbms_output.put_line to check your string in stmt variable looks like a valid create table command.

        It will be much easier, faster and more reliable to just
        create new_table as select * from existing_table where 0 = 1;
        • 16. Re: how to generete execute immediate of stmt
          JustinCave
          Can you explain why you need to generate the SQL statement manually from ALL_TAB_COLUMNS rather than using the DBMS_METADATA.GET_DDL procedure to get the DDL?

          Justin
          1 2 Previous Next