Drop the user if it exists
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>
Related examples in the same category