Script to truncate multiple tables using a single query

SQL> create table xyz (id number);
Table created.

SQL> create table t1 as select * from xyz;
Table created.

SQL>BEGIN

 FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('T1','XYZ'))

 LOOP

      EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i.table_name;

  END LOOP;

END;
/

SQL> select * from xyz;

no rows selected

SQL> select * from t1;

no rows selected

No comments:

Post a Comment