Drop all objects on schema in Oracle

Question

I'm trying to build a script to delete all objects on a database, so with the following select, build a procedure to execute that will leave the database completely clean (without having permissions to drop the database and creating it again). First remove all sequences, indexes, etc, and then the tables.

select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')

With that in mind I thought something like this could work:

BEGIN

    FOR i IN (select 'drop '||object_type||' '|| object_name || ';' from user_objects where object_type in ('VIEW','PACKAGE','SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX'))
    LOOP
        EXECUTE IMMEDIATE i;
    END LOOP;

END;
/

But I get:

Error report - ORA-06550: line 5, column 27: PLS-00382: expression is of wrong type ORA-06550: line 5, column 9: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Any idea how to perform this?

Answer 1

execute immediate needs a string (or varchar2) argument. You are giving it a cursor row. Give your selected value an alias and use that as the argument, and don't include the semicolon in the statement, like this:

begin
  for i in (select 'drop ' || object_type || ' ' || object_name as stmt
              from user_objects
             where object_type in ('VIEW', 'PACKAGE', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'INDEX')) loop
    execute immediate i.stmt;
  end loop;
end;
/