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.
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.