PL/SQL – Drop User inside BeginEnd block

oracleplsql

I have some experience with SQL Server but now I'm starting to work with PL SQL and I'm facing some problems because the difference between the two technologies.

I have some scripts to drop my database and when I need to delete my user I do something like this:

DEFINE OWNER = MYUSER

ALTER SESSION SET DDL_LOCK_TIMEOUT = 3600;

BEGIN
  FOR r IN (select sid,serial# from v$session where username=&OWNER)
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' || r.serial# || '''';
  END LOOP;

  DROP USER &OWNER CASCADE;
END;
/

However I'm getting this error:

Details:
ORA-06550: line 6, column 3: PLS-00103: Encountered the symbol \"DROP\" when expecting one of the following:

( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array (DBD ERROR:

error possibly near <*> indicator at char 189 in 'BEGIN FOR r IN (select
sid,serial# from v$session where username=MYUSER) LOOP EXECUTE
IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial#
|| ''''; END LOOP; <*>DROP USER MYUSER CASCADE; END; ')

I already tried to add a slash in line 8:

  END LOOP;
  /
  DROP USER &OWNER CASCADE;
END;
/

but the error still appearing, and I already tried to move DROP USER &OWNER CASCADE; to the end of the script but I'm still getting errors.

I can't understand what is wrong and I already read many links this about this, including:

What is the problem? Begin End don't allow empty lines? DROP USER &OWNER CASCADE; must be inside another Begin End?

Best Answer

Data Definition Language (DDL) Statements

The DDL statements are:

... DROP ... (All statements beginning with DROP) ...

PL/SQL does not support DDL as static SQL:

PL/SQL Static SQL

To run a DDL statement in PL/SQL, use dynamic sql:

PL/SQL Dynamic SQL

Example:

begin
  ...
  execute immediate 'DROP USER &OWNER CASCADE';
  ...
end;
/

Or just use:

BEGIN
  FOR r IN (select sid,serial# from v$session where username=&OWNER)
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' || r.serial# || '''';
  END LOOP;
END;
/

DROP USER &OWNER CASCADE;