Forum Stats

  • 3,824,946 Users
  • 2,260,442 Discussions
  • 7,896,356 Comments

Discussions

To drop multiple tables in a single line statement without any relationship with other tables.

kasimvali
kasimvali Member Posts: 2
edited Feb 10, 2020 3:26PM in Database Ideas - Ideas

Hi Team,

Firstly, thanks for giving this opportunity to share my ideas on Oracle Database.

I have developed a code to drop multiple tables in a single line statement without any relationship with other tables. Please refer the below code.

---create a type and then use it as a input parameter of a procedure.------

create or replace type table_ty is table of varchar2(30);

--By using type(table_ty) to pass multiple input values

create or replace PROCEDURE Drop_Mul_Tab(

    table_names IN table_ty

)

    IS

BEGIN

    FOR i IN table_names.first..table_names.last LOOP

             dbms_output.put_line('Table_name: '||table_names(i) );

             execute immediate 'drop table '||table_names(i);

             dbms_output.put_line('Table Name '||table_names(i)|| ' is dropped successfully');

END LOOP;

/

EXEC Drop_Mul_Tab(table_ty('EMP','DEP','STUDENTS','MARKS'));

As per above code i have tested in lower environment. Executed Successfully. Please refer below screenshot.

pastedImage_2.png

I hope this might be very useful to next versions of oracle to set as a pre-defined package.

For example we need to drop 'N' tables for that we have to write 'N' drop statements instead of that we using above code to drop 'N' tables in a single statement.

Kindly consider my code we together can share our thoughts to each other so that we can develop a better code for the next version of Oracle.

kasimvali
9 votes

Active · Last Updated

Comments

  • GregV
    GregV Member Posts: 3,085 Gold Crown

    Hi,

    I don't see it as something useful, especially considering in Oracle you don't drop tables that often (or maybe just for test purpose, not in production). I'd still prefer to drop a table on a single instruction basis as dropping a table should be done with caution.

  • Thanks for your response.

    I agree with you, it's very dangerous to drop and truncate multiple tables in production. Just lets consider this as an option to drop and truncate multiple unwanted or unnecessary tables in production or lower environment.