oracle – truncate tables based on select query

PL/SQL allows to execute sql statements using a FOR loop, looping through results of another query.

In below example we truncate all tables returned by another query:

-- Q1: empty all audit tables
--/
BEGIN
-- Disable constraints

-- Truncate tables returned by SELECT query
FOR entry IN (
SELECT table_name FROM user_tables WHERE table_name like '%AUDIT%' -- and rownum < 3 order by table_name
) LOOP
--EXECUTE IMMEDIATE 'select * from ' || entry.table_name || ' where rownum < 10';
EXECUTE IMMEDIATE 'truncate table ' || entry.table_name;
END LOOP;
-- Enable constraints

END;
/

Note: make sure to enclose overall query into “–/” and “/” for it to be executed correctly in some sql editors such as dbvisualiser

Alernatively, you can print the generated statements using DBMS_OUTPUT and then execute them:

--/
BEGIN
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
-- Disable constraints

-- Truncate tables returned by SELECT query
FOR entry IN (
SELECT table_name FROM user_tables WHERE table_name like '%AUDIT%' -- and rownum < 3 order by table_name
) LOOP
--EXECUTE IMMEDIATE 'select * from ' || entry.table_name || ' where rownum < 10';
--EXECUTE IMMEDIATE 'truncate table ' || entry.table_name;
DBMS_OUTPUT.PUT_LINE('truncate table ' || entry.table_name);
END LOOP;
-- Enable constraints

END;
/

Sources

Post a Comment

Your email is never published nor shared. You're allow to say what you want...