SQL> select*from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
J                              	TABLE
K                              	TABLE
U                              	TABLE
O                              	TABLE


SQL> create or replace PROCEDURE DROP_TABLES IS
  2  v_statement   VARCHAR2 (200);
  3  cursor cr is  select TABLE_NAME from user_tables;
  4  BEGIN
  5       FOR i IN cr
  6     LOOP
  7        v_statement :=
  8                  'drop table ' || i.TABLE_NAME || ' cascade constraints purge';
  9        EXECUTE IMMEDIATE v_statement;
 10     END LOOP;
 11  END;
 12  /
Procedure created.

SQL> exec DROP_TABLES
PL/SQL procedure successfully completed.


SQL>  select*from cat;
no rows selected