DECLARE CURSOR c1 IS SELECT table_name FROM user_tables; t_name USER_TABLES.TABLE_NAME%TYPE; drop_stmt VARCHAR2(100); BEGIN OPEN c1; LOOP FETCH c1 INTO t_name; EXIT WHEN c1%NOTFOUND; drop_stmt := 'DROP TABLE '||t_name; EXEC IMMEDIATE drop_stmt; END LOOP; END; /So the above will accomplish the same task as my previous response. But my question to you is, which solution is easier to implement? For the Oracle newbie, my solution is not only shorter, but more intuitive, especially if they do not know PL/SQL. Obviously, the PL/SQL solution is useful if the requirement is to develop a stored procedure that will implement this functionality, but that was not part of the original poster's question.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.