cursor syntax to fetch and drop tables in the db



  • I have a list of tables from a select query which gives me at least 50 tables daily . Need to drop them as they are temporary. I need a cursor to fetch and drop each one of them in a loop. I am using oracle Db.



  • OK then, you were already told that what you're doing isn't the best idea. However, you might have your reasons, asked a question and here's one option. See if it helps.

    In my database, Scott's sample schema is used as a playground and I frequently create/drop tables there. I'll keep EMP and DEPT, while other tables can be dropped (I don't need them anyway). tab_to_drop contains list of those tables (that's your "50 tables").

    SQL> create table tab_to_drop as
      2    select table_name
      3    from user_tab_columns
      4    where table_name not in ('EMP', 'DEPT');
    

    Table created.

    PL/SQL block that loops through that table and tries to drop each of those tables. Sometimes it succeeds, sometimes not - usually when there's a foreign key constraint involved. True - you can make the script way smarter and drop constraints first and tables next, or re-run this code.

    SQL> set serveroutput on
    SQL> begin
      2    for cur_r in (select tname from tab_to_drop) loop
      3      begin
      4        execute immediate 'drop table ' || cur_r.tname;
      5        dbms_output.put_line(cur_r.tname ||' dropped');
      6      exception
      7        when others then
      8          dbms_output.put_line(cur_r.tname ||' not dropped because of ' || sqlerrm);
      9      end;
     10    end loop;
     11  end;
     12  /
    ANAGRAFICA dropped
    COURSE dropped
    
    ORDERS dropped
    OWNER not dropped because of ORA-02449: unique/primary keys in table referenced by foreign keys
    PIECE_OF_ART dropped
    
    XYZ dropped
    

    PL/SQL procedure successfully completed.

    SQL>




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2