This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 11, 2012 1:16 PM by Justin Cave Go to original post RSS
  • 15. Re: how to generete execute immediate of stmt
    6363 Guru
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points