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

    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
    Where the Surf Meets the Turf @Del Mar, CA
    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
    Provided Answers: 1
    This is a simple example of reading the system view(all_tables) and truncating the tables owned by the supplied user:
    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;
        for table_rec in table_cur
          v_sql  :=  c_trunc || table_rec.table_name || ';';
          execute immediate v_sql;
        end loop;
          when others then
            dbms_output.put_line( to_char( sqlcode ) || ':' || sqlerrm );


Posting Permissions

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