Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    28

    Unanswered: Reading tablenames and dynamically creating sql statements

    We run oracle 10g and I am curious if there's any way to read in tablenames from a list (say a table containing tablenames) then loop through the tables and perform sql statements using those tablenames. For example define a cursor to select tablename from all_tables where user = 'userid'. Then Loop through the cursor and say truncate table cursor.tablename or delete from cursor.tablename where create_dt <= (sysdate-30). I'm guessing there has to be a way to do this but I haven't found a method yet. Thaks for any help!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It can be accomplished by (ab)using EXECUTE IMMEDIATE within a PL/SQL procedure

    The result is a non-scalable piece of code & may be a latent bug generator in the future.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    This is a simple example of reading the system view(all_tables) and truncating the tables owned by the supplied user:
    Code:
    create or replace procedure trunc_tables_pkg(p_user  in  varchar2) is
      c_trunc constant varchar2(   15 )  := 'truncate table ';
      v_sql            varchar2(  512 );
      cursor table_cur is
        select table_name
         from  all_tables
        where  owner  =  upper( p_user )
        order by table_name;
      begin
        for table_rec in table_cur
        loop
          v_sql  :=  c_trunc || table_rec.table_name || ';';
          execute immediate v_sql;
        end loop;
        exception
          when others then
            dbms_output.put_line( to_char( sqlcode ) || ':' || sqlerrm );
      end;


    hth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •