Drop user, create user and grant permission with PL/SQL code : Execute immediate « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Execute immediate

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> DECLARE
  2     v_count       INTEGER        := 0;
  3     v_statement   VARCHAR2 (500);
  4  BEGIN
  5
  6     SELECT COUNT (1) INTO v_count FROM dba_users
  7      WHERE username = UPPER ('&username');
  8
  9     IF v_count != 0
 10     THEN
 11        EXECUTE IMMEDIATE ('DROP USER &username CASCADE');
 12     END IF;
 13
 14     v_count := 0;
 15
 16     v_statement :=
 17           'CREATE USER &username IDENTIFIED BY oracle'
 18        || ' DEFAULT TABLESPACE &default_ts'
 19        || ' TEMPORARY TABLESPACE &temp_ts'
 20        || ' QUOTA UNLIMITED ON &default_ts'
 21        || ' ACCOUNT UNLOCK';
 22
 23     EXECUTE IMMEDIATE (v_statement);
 24
 25     -- Grant permissions
 26     EXECUTE IMMEDIATE ('GRANT connect, resource TO &username');
 27     EXECUTE IMMEDIATE ('GRANT CTXAPP TO &username');
 28
 29     DBMS_OUTPUT.put_line ('  ');
 30     DBMS_OUTPUT.put_line ('User &username created successfully');
 31     DBMS_OUTPUT.put_line ('  ');
 32
 33  EXCEPTION
 34     WHEN OTHERS
 35     THEN
 36        DBMS_OUTPUT.put_line (SQLERRM);
 37        DBMS_OUTPUT.put_line ('   ');
 38  END;
 39  /
User plsql created successfully
SQL>
SQL> SET FEEDBACK ON TERMOUT ON
SQL>
SQL>
SQL>