CREATE OR REPLACE PROCEDURE clean
IS
 BEGIN
  FOR tn IN
   ( SELECT table_name FROM all_tables
     WHERE table_name LIKE 'TMP_%')
  LOOP
   execute immediate 'DROP TABLE ' || tn.table_name;
  END LOOP;
END;
/
