deleting multiple tables

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

Sanctus.

Delete from multiple tables or drop multiple tables?

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.

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

Quote:

---

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.

---

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.

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

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;
/