I need to create procedure which will delete all data from tables in one schema. I try something like that
CREATE OR REPLACE PROCEDURE CLEAR_ALL
IS
sql_truncate VARCHAR2(50);
cursor c1 is
SELECT table_name
FROM all_tables
WHERE owner = 'KARCHUDZ_S';
BEGIN
sql_truncate := 'TRUNCATE TABLE :text_string';
FOR table_name in c1
LOOP
EXECUTE IMMEDIATE sql_truncate USING table_name;
END LOOP;
END CLEAR_ALL;
But it gives me two errors which i cannot understand and fix.
Error(13,7): PL/SQL: Statement ignored
Error(13,44): PLS-00457: Statment must be type of SQL <-- (This error i had to translate, cause i use University Oracle 11g base which have Polish lang)
Why not just generate the statement and call it, like this?
CREATE OR REPLACE PROCEDURE CLEAR_ALL
IS
vs_statement VARCHAR2(100);
cursor c1 is
SELECT table_name
FROM all_tables
WHERE owner = 'KARCHUDZ_S';
BEGIN
FOR table_rec in c1
LOOP
vs_statement := 'TRUNCATE TABLE ' || table_rec.table_name;
EXECUTE IMMEDIATE vs_statement;
END LOOP;
END CLEAR_ALL;