This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 11, 2012 1:16 PM by Justin Cave RSS

how to generete execute immediate of stmt

977981 Newbie
Currently Being Moderated
hello, I wrote few lines od code:
=========================================
declare
stmt varchar(30000);

begin

stmt := 'select column_name, data_type, data_length, data_precision, data_scale
from all_tab_columns
where owner in(''HR'')
and table_name in (''EMPLOYEES'')
';

execute immediate ;

end;
========================================

and how can I use execute immediate CREATE TABLE emp_copy of stmt?
  • 1. Re: how to generete execute immediate of stmt
    LPS Journeyer
    Currently Being Moderated
    declare
    stmt varchar(30000);

    begin

    stmt := ' create table emp_copy as select column_name, data_type, data_length, data_precision, data_scale
    from all_tab_columns
    where owner in(''HR'')
    and table_name in (''EMPLOYEES'')
    ';
    dbms_output.put_line('STATEMENT PREPARED'||stmt);

    execute immediate stmt;

    end;
  • 2. Re: how to generete execute immediate of stmt
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    Please read SQL and PL/SQL FAQ

    Additionally when you put some code or output please enclose it between two lines starting with {noformat}
    {noformat}
    
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    Check also the syntax of <a href="http://docs.oracle.com/cd/E14072_01/appdev.112/e10472/executeimmediate_statement.htm">EXECUTE IMMEDIATE</a>. Your code is not working as you did not specify what you want to execute.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 3. Re: how to generete execute immediate of stmt
    user in Explorer
    Currently Being Moderated
    your question is not clear
    please read the instructions in the below link and rewrite the question

    SQL and PL/SQL FAQ
  • 4. Re: how to generete execute immediate of stmt
    971895 Journeyer
    Currently Being Moderated
    Try like ..
    declare
    stmt varchar(30000);
    begin
    
    begin 
    execute immediate 'drop table emp_copy';
    exception
    when others then 
    null;
    end;
    
    stmt := ' create table emp_copy as select column_name, data_type,
    data_length, data_precision, data_scale
    from all_tab_columns
    where owner in(''HR'')
    and table_name in (''EMPLOYEES'')';
    dbms_output.put_line('STATEMENT PREPARED'||stmt);
    execute immediate stmt;
    end;
  • 5. Re: how to generete execute immediate of stmt
    padders Pro
    Currently Being Moderated
    Didn't you ask this last week?

    What was wrong with the answer you got then?
  • 6. Re: how to generete execute immediate of stmt
    padders Pro
    Currently Being Moderated
    begin 
    execute immediate 'drop table emp_copy';
    exception
    when others then 
    null;
    end;
    If you are trying to handle a specific exception WHEN OTHERS THEN NULL is not how you do it.

    http://tkyte.blogspot.nl/2007/03/dreaded-others-then-null-strikes-again.html
  • 7. Re: how to generete execute immediate of stmt
    977981 Newbie
    Currently Being Moderated
    ok so it doesn't work as i though.
    when i run stmt select part i'm getting column_name like employees_id etc and i need to create copy table with the same requirements such as for eployee_id i got number NUMBER (6,0) Not null. but instead of creating that copy table myself by reading what show my runned select i want create table where all requirements and column name will be downloaded from all_tab_columns
  • 8. Re: how to generete execute immediate of stmt
    LPS Journeyer
    Currently Being Moderated
    declare
    stmt varchar(30000);

    begin
    stmt := ' create table emp_copy as select * from EMPLOYEES where 1 = 2';

    dbms_output.put_line('STATEMENT PREPARED'||stmt);

    execute immediate stmt;

    end;

    it will create just create a empty table without any records......where 1 = 2
  • 9. Re: how to generete execute immediate of stmt
    977981 Newbie
    Currently Being Moderated
    yes i know, i got that question once, but i really need to use all_tab_columns table
  • 10. Re: how to generete execute immediate of stmt
    LPS Journeyer
    Currently Being Moderated
    then you specify the data type in the create statement since you know the data type and size.
  • 11. Re: how to generete execute immediate of stmt
    977981 Newbie
    Currently Being Moderated
    ok select question to table all_tab_columns about table employees generetes me that:

    http://img5.imageshack.us/img5/9591/beztytuuyn.jpg

    and thanks to all that requirements i need to create copy table
  • 12. Re: how to generete execute immediate of stmt
    6363 Guru
    Currently Being Moderated
    Duplicate thread

    {message:id=10727991}

    - Why waste people's time posting the same question again if you do not attempt to understand any answers?
    - Is this homework?
    - If it is homework, this is a discussion forum not a free homework service and since you have not learned anything since the last time you posted so you should try a different learning method, such as studying.

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#i2095331
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01115
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#i33888
  • 13. Re: how to generete execute immediate of stmt
    977981 Newbie
    Currently Being Moderated
    i got clue that it should look more or less like this:

    stmt:='create table '||name||'(';
    for x in (select * from all_tab_columns where table_name=name) loop

    stmt:=stmt||x.column_name||' '||x.data_type||','
    end loop;
    stmt:=stmt||')';
    execute immediate stmt;
  • 14. Re: how to generete execute immediate of stmt
    sb92075 Guru
    Currently Being Moderated
    bart_k_pl wrote:
    i got clue that it should look more or less like this:

    stmt:='create table '||name||'(';
    for x in (select * from all_tab_columns where table_name=name) loop

    stmt:=stmt||x.column_name||' '||x.data_type||','
    end loop;
    stmt:=stmt||')';
    execute immediate stmt;
    DDL should be static & reside within code repository.
1 2 Previous Next

Legend

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