1 2 Previous Next 27 Replies Latest reply: Oct 15, 2010 6:54 PM by JustinCave RSS

    For Each Loop PL/SQL

    Kodiak_Seattle
      I am slowly learning PL/SQL, I have some programming experience using Visual Basic for Excel, so I am not totally in the dark, but not really sure :-)

      Here is what I am trying to do, am on 10g by the way.

      I have about 10+ Tables in my schema, with pretty much the same structure and I know that I could use plain SQL to do this with, but I want to learn PL/SQL.

      So, I have 10+ tables where I have to add a new field of type varchar2(100), I was wondering if I could employ the FOR LOOP or maybe something better, list all the Tables that I need to ALTER and say:

      For each Table from list
      LOOP
      alter table table_name add KEY varchar2(100);
      END LOOP;

      I have the full list of the Tables, what do you guys think ?
        • 1. Re: For Each Loop PL/SQL
          MaxymK
          declare
            cursor c_t is select table_name from user_tables where ... order by table_name;
            szSql varchar2(2048);
          begin
            for rec in c_t loop 
              szSql := 'alter table '||rec.table_name||' add (your_column varchar2(100))';
              dbms_output.put_line(szSql);
              execute immediate szSql;
            end loop;
          end;
          /
          • 2. Re: For Each Loop PL/SQL
            reddy1986
            for i in select table_name from user_tables
            loop
            
            alter table i.table_name add column......
            
            end loop
            iam not sure but, try this code....if the stmt in loop shows an error then use "execute immediate" before the stmt

            Edited by: kiran on Oct 14, 2010 3:54 PM
            • 3. Re: For Each Loop PL/SQL
              Kodiak_Seattle
              Thanks guys, I will try both and report back, right now preparing the list of my tables that I want to alter.

              :-)
              • 4. Re: For Each Loop PL/SQL
                Kodiak_Seattle
                What good GUI tool exists for de-bugging ? Not proficient in SQL*Plus command line yet. Downloading SQL Developer, guess we'll see here soon how that works.
                • 5. Re: For Each Loop PL/SQL
                  reddy1986
                  TOAD..
                  • 6. Re: For Each Loop PL/SQL
                    Kodiak_Seattle
                    So this is a PL/SQL block right ? SQL Developer don't seem to be able to debug this just Functions and Procedures

                    What I dont get is where do I list the Tables that I want to alter ? in the CODE ?
                    • 7. Re: For Each Loop PL/SQL
                      Sven W.
                      If you want to practice PL/SQL then this is the wrong task.

                      You would need to use dynamic SQL (execute immediate or dbms_sql) for this, but usually you should avoid dynamic SQL for many reasons.
                      As you already mentioned this can be easily done with SQL.

                      What I do in such cases is to build a select that returns me the 10+ alter table statements.
                      The oracle data dictionary (e.g. the view user_tables) has enough information to build such a select based on your requirements.
                      Then save the result into a file and run the file as a script in sql*plus.

                      example untested
                      select 'alter table '||t.table_name||' add KEY varchar2(100);'
                      from user_tables t
                      where t.table_name like 'XYZ%'
                      and t.table_name not in (select c.table_name from user_tab_columns c where column_name = 'KEY');
                      Changes to the data structure in your database should not be done programatically (e.g by pl/sql) but should be entered into your version control system so that you can run the same script against your development database, against your test database and later against your production database.
                      • 8. Re: For Each Loop PL/SQL
                        Kodiak_Seattle
                        Hmmm, seems like a lot to type to alter a table and add a column, might as well do this using regular SQL.

                        So much for PL in SQL.
                        • 9. Re: For Each Loop PL/SQL
                          SomeoneElse
                          So much for PL in SQL.
                          So much for doing DDL in pl/sql.
                          • 10. Re: For Each Loop PL/SQL
                            Kodiak_Seattle
                            I guess, I should be able to declare a array as a list, list all the tables that I want to alter and loop.

                            Restrictions everywhere! :- )

                            Most documentation on this subject from oracle is cryptic, collections, arrays, no good examples.
                            • 11. Re: For Each Loop PL/SQL
                              SomeoneElse
                              You were shown a few good examples of doing what you wanted.

                              Didn't those work?

                              No need for arrays and all that. You're over-complicating things.
                              • 12. Re: For Each Loop PL/SQL
                                Kodiak_Seattle
                                no, those don't work, it makes no sense to re-run this PL/SQL 20 times for 20 tables, since I have all the names of the 20 tables, I wanted to list them into a variable like maybe:

                                list_of_tables varchar2(1000) := 'table1,table2,table3,table4,.......'

                                and then loop those, anyways, that's what I will try next... The tables are in test mode, so if I screw up, no big deal, they are all backed up.

                                I get lots of errors from the examples above.
                                • 13. Re: For Each Loop PL/SQL
                                  JustinCave
                                  If you want to hard-code a list of table names, rather than having some criteria to select the names from the data dictionary, you can do something like
                                  SQL> declare
                                    2    type table_name_arr is table of varchar2(30);
                                    3    table_names table_name_arr := table_name_arr( 'EMP', 'DEPT' );
                                    4  begin
                                    5    for i in 1..table_names.count
                                    6    loop
                                    7      execute immediate 'ALTER TABLE ' || table_names(i) ||
                                    8                        '  ADD( key varchar2(100) )';
                                    9    end loop;
                                   10  end;
                                   11  /
                                  
                                  PL/SQL procedure successfully completed.
                                  Justin
                                  • 14. Re: For Each Loop PL/SQL
                                    Kodiak_Seattle
                                    Justin, let me try that, thank you!
                                    1 2 Previous Next