1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Please help

Discussion in 'SQL PL/SQL' started by chaitanya.krishna, Apr 2, 2015.

  1. chaitanya.krishna

    chaitanya.krishna Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have written below code to drop the tables.

    declare
    tSQLstmt VARCHAR2(1000);
    Table_name varchar2(30) := 'emp';
    BEGIN
    tSQLstmt := 'DROP TABLE '||Table_name;
    DBMS_OUTPUT.Put_Line(tSQLstmt);
    EXECUTE IMMEDIATE tSQLstmt ;
    EXCEPTION
    WHEN OTHERS THEN NULL;
    END;

    It is working for one table.

    if i need to drop four tables (emp,emp1,emp2,emp3) at a time by hard coding the values using the same code is it possible.
    If not please suggest an alternative
     
  2. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yess..

    Code (SQL):


    SET SERVEROUTPUT ON
    DECLARE
    TABLE_NAME VARCHAR2(10):= 'emp';
    BEGIN
    FOR I IN 0..3 LOOP
    IF I=0 THEN
     EXECUTE IMMEDIATE  
        'DROP TABLE ' || TABLE_NAME;
    ELSE
     EXECUTE IMMEDIATE  
        'DROP TABLE ' || TABLE_NAME || I;
    END IF;
     END loop;
    END;

     
     
  3. chaitanya.krishna

    chaitanya.krishna Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi
    Thanks for the response.
    I want to drop 4 tables at the same time by hard coding the table names.
    for ex :
    EXECUTE IMMEDIATE 'DROP TABLE FACT_REPGEN_PEER_TEMP';
    EXECUTE IMMEDIATE 'DROP TABLE FACT_EXPGEN_PEER_TEMP';
    EXECUTE IMMEDIATE 'DROP TABLE ASSET_DIM_TEMP';
    EXECUTE IMMEDIATE 'DROP TABLE AUDIT_PEER_ADJ';

    I want to drop these four tables using the code which i have sent you.

    --
    Chaitanya
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes, you can drop all the 4 tables at a time using the 4 EXECUTE IMMEDIATE scripts which you've written..
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Or else you can use the Pl/sql code below.,

    Code (SQL):


    BEGIN

      FOR REC IN (SELECT TABLE_NAME
                  FROM   user_TABLES
                  WHERE  TABLE_NAME IN ('FACT_REPGEN_PEER_TEMP','FACT_EXPGEN_PEER_TEMP','ASSET_DIM_TEMP','AUDIT_PEER_ADJ')
                 )
      loop
        EXECUTE immediate 'drop table '||rec.TABLE_NAME;
      END LOOP;            
    END;


     
     
  6. PR14

    PR14 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Yes.. I you can use the same code.

    Code (SQL):

    DECLARE
    tSQLstmt VARCHAR2(1000);
    TABLE_NAME varchar2(30) := 'emp,emp1,emp2,emp3';
    BEGIN
    tSQLstmt := 'DROP TABLE '||TABLE_NAME;
    DBMS_OUTPUT.Put_Line(tSQLstmt);
    EXECUTE IMMEDIATE tSQLstmt ;
    EXCEPTION
    WHEN OTHERS THEN NULL;
    END;
     
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes You can achieve that using the code below....



    Code (SQL):


    SET serveroutput ON;
    DECLARE
    tSQLstmt VARCHAR2(1000);
    TABLE_NAME varchar2(30) := 'emp,emp1,emp2,emp3';
    BEGIN
    FOR REC IN (SELECT REGEXP_SUBSTR(TABLE_NAME,'[^,]+',1,level) AS TABLE_NAME FROM DUAL
    CONNECT BY level<=LENGTH(regexp_replace(TABLE_NAME,'[^,]'))+1) LOOP
    tSQLstmt := 'DROP TABLE '||REC.TABLE_NAME;
    DBMS_OUTPUT.Put_Line(tSQLstmt);
    EXECUTE immediate TSQLSTMT ;
    END loop;
    EXCEPTION
    WHEN OTHERS THEN NULL;
    END;


     
     
  8. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Vicky,

    It seems the logic used in the code has bug
    Code (SQL):

    SELECT REGEXP_SUBSTR('emp,emp1,emp2,emp3,emp4,emp5','[^,]+',1,level) AS TABLE_NAME,level FROM DUAL
    CONNECT BY level<=INSTR('emp,emp1,emp2,emp3,emp4,emp5',',')
    The output I am getting is
    TABLE_NAME LEVEL

    emp 1
    emp1 2
    emp2 3
    emp3 4

    Can you once check from your side
     
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes.. Cud U try the below qry's..

    Code (SQL):


    SELECT REGEXP_SUBSTR('emp,emp1,emp2,emp3,emp4,emp5,emp6,emp7','[^,]+',1,level) AS TABLE_NAME,level FROM DUAL
    CONNECT BY level<=REGEXP_COUNT('emp,emp1,emp2,emp3,emp4,emp5,emp6,emp7',',')+1;


    TABLE_NAME                             LEVEL
    -------------------------------------- -----
    emp                                        1
    emp1                                       2
    emp2                                       3
    emp3                                       4
    emp4                                       5
    emp5                                       6
    emp6                                       7
    emp7                                       8

     

    or..


    Code (SQL):


    SELECT REGEXP_SUBSTR('emp,emp1,emp2,emp3,emp4,emp5,emp6,emp7','[^,]+',1,level) AS TABLE_NAME,level FROM DUAL
    CONNECT BY level<=LENGTH(regexp_replace('emp,emp1,emp2,emp3,emp4,emp5,emp6,emp7','[^,]'))+1;


    TABLE_NAME                             LEVEL
    -------------------------------------- -----
    emp                                        1
    emp1                                       2
    emp2                                       3
    emp3                                       4
    emp4                                       5
    emp5                                       6
    emp6                                       7
    emp7                                       8

     
     
    RG Hegde likes this.
  10. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Yes. It is working fine...