Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to delete all data in all tables in one time ?!

544191Mar 6 2008 — edited Oct 1 2008
1 CREATE OR REPLACE PROCEDURE delete_all_data
2 IS
3 v_statement VARCHAR2 (200);
4 BEGIN
5 FOR i IN (SELECT *
6 FROM user_tables)
7 LOOP
8 v_statement :=
9 'delete table ' || i.table_name ;
10 EXECUTE IMMEDIATE v_statement;
11 END LOOP;
12 commit;
13* END;
SQL> /

Procedure created.

SQL> exec delete_all_data
BEGIN delete_all_data; END;

*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at "DE2.DELETE_ALL_DATA", line 10
ORA-06512: at line 1




I made the previous code , but it's didn't work with me .... any help for this problem please ?!

I'm just reminder ..... all what I need ,that delete all data in all tables in one time only .

I'm waiting for the answer ..... and thanks in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 29 2008
Added on Mar 6 2008
7 comments
7,164 views