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>