1 2 Previous Next 16 Replies Latest reply: Dec 11, 2012 3:16 PM by JustinCave RSS

    how to generete execute immediate of stmt

    977981
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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