Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    5

    Unanswered: how to dynamic drop some table?

    In our system we create some tables that tablename like "RD_1233"."RD_" is these tables prefix name.So when I uninstall our system, I need use SQL to drop these table first.
    I can use this SQL query all table that I need drop:

    select t.owner, t.table_name from all_tables t
    where
    t.owner = 'HWREPORT' and t.table_name like 'RD_%'

    But I don't know how to use SQL drop all this table.

  2. #2
    Join Date
    Jun 2004
    Posts
    1
    Hi Arthur,

    Spool this sql to a file and execute this fspool file.
    set head off
    set pagesize 1000
    set pau off

    SELECT 'Drop Table '||owner||'.'||table_name||';'
    FROM all_tables
    WHERE owner = 'HWREPORT' and table_name like 'RD_%'

    Output of this SQL will be :
    Drop Table HWREPORT.RD_1233;
    Drop Table HWREPORT.RD_1234;
    Drop Table HWREPORT.RD_1235;

    I hope this should help you...

    N'joi,
    Mayu

  3. #3
    Join Date
    Jun 2004
    Posts
    1

    Thumbs up how to dynamic drop some table?

    This will help you to delete the tables
    DECLARE
    CURSOR DELETE_TABLE IS
    SELECT OWNER, TABLE_NAME
    FROM ALL_TABLES
    WHERE OWNER = 'HWREPORT'
    AND TABLE_NAME LIKE 'RD/_%' ESCAPE '/';
    BEGIN
    FOR DROP_TABLE IN DELETE_TABLE
    LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || DROP_TABLE.OWNER || '.' || DROP_TABLE.TABLE_NAME;
    END LOOP;
    END;

  4. #4
    Join Date
    Jul 2003
    Posts
    5
    Thanks mayu and rahuldesh, I have solved this problem.I use a procedure.

    CREATE OR REPLACE PROCEDURE DROPHWREPORTTABLE
    IS
    TYPE TBLNAME_ARRAY_TYPE IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
    TBLNAME_ARRAY TBLNAME_ARRAY_TYPE;
    DROPSTR VARCHAR2(200);
    BEGIN
    SELECT TABLE_NAME BULK COLLECT
    INTO TBLNAME_ARRAY
    FROM ALL_TABLES
    WHERE OWNER = 'HWREPORT';

    IF TBLNAME_ARRAY.LAST<>0 THEN
    FOR I IN TBLNAME_ARRAY.FIRST..TBLNAME_ARRAY.LAST LOOP
    DROPSTR:=' DROP TABLE HWREPORT.'||TBLNAME_ARRAY(I);
    EXECUTE IMMEDIATE DROPSTR;
    END LOOP;
    END IF;
    END;

    In toad and sqlplus comand I have excuted this procedure successfully.But I don't know how to excute it in a sql file.

    --drop all table in hwreport
    exec DROPHWREPORTTABLE;

    --DROP TABLE REPORT;
    CREATE TABLE REPORT (
    ID INT NOT NULL,
    LTS INT DEFAULT 1,
    CONSTRAINT PK_REPORT PRIMARY KEY (ID)
    );
    commit;

    I save these SQL as initdb_report.sql
    In sqlplus I excute it :
    SQL> @initdb_report.sql

    It return some error :
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00201: identifier 'DROPHWREPORTTABL' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

Posting Permissions

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