PL/SQL EXECUTE IMMEDIATE inside LOOP (procedure to truncate all tables in schema)

Question

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)

Answer 1

You can't use bind variables (i.e. your using clause) as a placeholder for an object name. If you could, you wouldn't need to use dynamic SQL in the first place. You'll have to use concatenation or substitution instead:

CREATE OR REPLACE PROCEDURE CLEAR_ALL
IS
  sql_truncate CONSTANT VARCHAR2(50) := 'TRUNCATE TABLE [text_string]';

  cursor c1 is
   SELECT table_name
   FROM all_tables 
   WHERE owner = 'KARCHUDZ_S';
BEGIN
  FOR row in c1
   LOOP
      EXECUTE IMMEDIATE replace(sql_truncate, '[text_string]', row.table_name);
   END LOOP;
END CLEAR_ALL;

Answer 2

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;