-
how can i delete multiple tables in a single DML
statement
Can anybody help..
Sanctus.
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
drop 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
sanctus
-
Re: drop multiple tables
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.
-
dropping tables
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.
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;
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|