Drop the user if it exists : Drop User « User Privilege « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. User Privilege
3. Drop User

SQL>
SQL> DEF username = plsql
SQL> DEF default_ts = USERS
SQL> DEF temp_ts = TEMP
SQL>
SQL> SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
SQL>
SQL> SPOOL create_user.log
SQL>
SQL> DECLARE
  2     v_count       INTEGER        := 0;
  3     v_statement   VARCHAR2 (500);
  4  BEGIN
  5
  6    
  7
  8     SELECT COUNT (1) INTO v_count FROM dba_users WHERE username = UPPER ('&username');
  9
 10     IF v_count != 0
 11     THEN
 12        EXECUTE IMMEDIATE ('DROP USER &username CASCADE');
 13     END IF;
 14
 15     v_count := 0;
 16
 17  EXCEPTION
 18     WHEN OTHERS
 19     THEN
 20        DBMS_OUTPUT.put_line (SQLERRM);
 21        DBMS_OUTPUT.put_line ('   ');
 22  END;
 23  /
SQL>
SQL> SET FEEDBACK ON TERMOUT ON
SQL>
SQL> SET FEEDBACK ON
SQL>
SQL> SPOOL OFF
SQL>
SQL>