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 v_statement := 'CREATE USER &username IDENTIFIED BY oracle'
9 || ' DEFAULT TABLESPACE &default_ts'
10 || ' TEMPORARY TABLESPACE &temp_ts'
11 || ' QUOTA UNLIMITED ON &default_ts'
12 || ' ACCOUNT UNLOCK';
13
14 EXECUTE IMMEDIATE (v_statement);
15
16
17 EXECUTE IMMEDIATE ('GRANT connect, resource TO &username');
18 EXECUTE IMMEDIATE ('GRANT CTXAPP TO &username');
19
20 DBMS_OUTPUT.put_line (' ');
21 DBMS_OUTPUT.put_line ('User &username created successfully');
22 DBMS_OUTPUT.put_line (' ');
23
24 EXCEPTION
25 WHEN OTHERS
26 THEN
27 DBMS_OUTPUT.put_line (SQLERRM);
28 DBMS_OUTPUT.put_line (' ');
29 END;
30 /
User plsql created successfully
SQL>
SQL> SET FEEDBACK ON TERMOUT ON
SQL>
SQL>
SQL> SPOOL OFF
SQL>
SQL>