This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Oct 15, 2010 4:54 PM by Justin Cave RSS

For Each Loop PL/SQL

Kodiak_Seattle Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    TOAD..
  • 6. Re: For Each Loop PL/SQL
    Kodiak_Seattle Newbie
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    So much for PL in SQL.
    So much for doing DDL in pl/sql.
  • 10. Re: For Each Loop PL/SQL
    Kodiak_Seattle Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Justin, let me try that, thank you!
1 2 Previous Next

Legend

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