I have more than 40 tables in RATOR_MONITORING schema for which the table name is starting from 'TEMP_'. I want to delete data from all such tables at once in a single query instead of using delete statement for each and every table. I dont even want to generate statements. I can create anonymous block if required but dont know how to do that. I tried below query but its not working.
Delete from RATOR_MONITORING_CONFIGURATION.'%TEMP_';
If you want to delete all the rows, then better use TRUNCATE, it will reset the high watermark. But remember, truncate is a DDL statement, and thus there will be an implicit commit; With DELETE you can commit manually after validation.
Although, I would not do that in a production environment. If it is something you are doing in test environment to build test data, then you could (ab)use EXECUTE IMMEDIATE.
For example, execute the following anonymous block as RATOR_MONITORING user:
DECLARE
v_sql VARCHAR2(100);
BEGIN
FOR i IN
(SELECT table_name FROM user_tables where table_name like 'TEMP%'
)
LOOP
v_sql := 'TRUNCATE TABLE '||i.table_name;
EXECUTE immediate v_sql;
END LOOP;
END;
/
By the way, using a good text editor, it won't take more than a minute to build DELETE/TRUNCATE statements and do it in pure SQL.
user_tables will have all the table_names, from which you will filter those tables starting with TEMP%. Without mentioning the table_name explicitly, you cannot execute the delete statement?
EXECUTE IMMEDIATEstatementEXECUTE IMMEDIATE.