DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: deleting multiple tables

  1. #1
    Join Date
    May 2001
    Posts
    39

    Cool

    how can i delete multiple tables in a single DML
    statement
    Can anybody help..

    Sanctus.
    sanctus

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Delete from multiple tables or drop multiple tables?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You can do neither of those two things (delete from or drop multiple tables) with a single statement, unless you want to drop a user or tablespace.

    You can write a script or stored procedure that will do the delete from multiple tables.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    May 2001
    Posts
    39

    Unhappy drop multiple tables

    hello mr.julian or mr.jmodic i want to drop multiple tables
    can u tell me how can i go about..
    i would be obliged..

    sanctus
    sanctus

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: drop multiple tables

    As jmodic said you should write a script using dynamic SQL for example. If you want to drop a small amount of tables (5-10), I would issue the DROP TABLE statement 5-10 times. If you want to drop many tables based on a certain criteria, then write an .sql script which starts with an explicite cursor selecting the tables from DBA_TABLES for example and then drop them dynamically within a loop.

  6. #6
    Join Date
    May 2001
    Posts
    39

    dropping tables

    hello sir,
    I did write a Pl/sql as follows;

    declare
    var_tt constant number :=8;
    cursor c1 is select table_name from dba_tables
    where table_name like 'D'||var_tt||'%';
    begin
    for x in c1
    loop

    DROP TABLE x.TABLE_NAME;
    # insert into dummy_del(name) values(x.table_name);
    end loop;
    commit;
    END;

    the problem here is that it shows error on the DROP TABLE
    statement; but if i try the insert option (marked with # sign)
    it performs perfectly right.

    So how do i implement the drop table option in the loop

    Sanctus.
    sanctus

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot issue DDL like that in PL/SQL, you have to use either DBMS_SQL in version 7 and 8 or execute immediate in 8i to execute DDL

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    This script will do your job:

    --
    set serveroutput on
    set verify off
    prompt
    --
    DECLARE
    --
    w_owner varchar2(30); w_table varchar2(30);
    sql_text varchar2(256); sql_text2 varchar2(256);
    Dynamic_Cursor integer; Dynamic_Cursor2 integer;
    dummy integer; dummy2 integer;
    table_usage pls_integer;
    w_lf_rows_len table_stats.lf_rows_len%TYPE;
    w_broi pls_integer := 0;
    --
    cursor trii is
    select table_name from dba_tables
    where table_name like 'D'||var_tt||'%';
    --
    BEGIN
    --
    dbms_output.enable(60000);
    open trii;
    fetch trii into w_table;
    while trii%FOUND loop
    Dynamic_Cursor := dbms_sql.open_cursor;
    sql_text := 'drop table '||w_table';
    dbms_sql.parse(Dynamic_Cursor, sql_text, dbms_sql.native);
    dummy := dbms_sql.execute(Dynamic_Cursor);
    dbms_sql.close_cursor(Dynamic_Cursor);
    fetch trii into w_table;
    end loop;
    close trii;
    --
    END;
    /


Posting Permissions

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


Click Here to Expand Forum to Full Width