PL/SQL - Drop User inside BeginEnd block

Question

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:

• https://www.tutorialspoint.com/plsql/plsql_basic_syntax.htm
• https://stackoverflow.com/questions/15664924/dropping-connected-users-in-oracle-database
• https://stackoverflow.com/questions/20334067/pls-00103-encountered-the-symbol-create

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

Answer 1

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;

• Michael Kutz: You might want to LOCK the account so someone doesn't try to login while the code is looping. (eg a Connection Pooling system)

Answer 2

The code had two problems.

1. Like @BalazsPapp said DROP USER command should be at the end of the script or inside a execute immediate
2. Missing quotes on v$session select. When moving DROP USER command to the end of the script start appearing other error because of the invalid use of 'R'. But this error was caused for the missing quotes.

Fixing the code, now I have this code working:

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;
END;
/
DROP USER &OWNER CASCADE;