I use Oracle XE for the sole purpose of developing PHP applications and version 11g has apparently lost the GUI tool to manage users which 10g used to have so I'd like to prepare a code snippet to create users from command line. I'm trying to define variables so I don't need to type the same user name 16 times but I can't get the syntax right:
DECLARE
my_user VARCHAR2(30) := 'foo';
my_password VARCHAR2(9) := '1234';
BEGIN
CREATE USER my_user IDENTIFIED BY my_password DEFAULT TABLESPACE users;
GRANT CONNECT, RESOURCE TO my_user;
GRANT CREATE DATABASE LINK TO my_user;
GRANT CREATE MATERIALIZED VIEW TO my_user;
GRANT CREATE PROCEDURE TO my_user;
GRANT CREATE PUBLIC SYNONYM TO my_user;
GRANT CREATE ROLE TO my_user;
GRANT CREATE SEQUENCE TO my_user;
GRANT CREATE SYNONYM TO my_user;
GRANT CREATE TABLE TO my_user;
GRANT CREATE TRIGGER TO my_user;
GRANT CREATE TYPE TO my_user;
GRANT CREATE VIEW TO my_user;
GRANT SELECT_CATALOG_ROLE TO my_user;
GRANT SELECT ANY DICTIONARY TO my_user;
END;
/
CREATE USER my_user IDENTIFIED BY my_password DEFAULT TABLESPACE users; * ERROR en línea 5: ORA-06550: line 5, column 2: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge
Is it forbidden to use a CREATE USER
statement from within a PL/SQL block or I simply made a silly typo? Is it mandatory to use SQL*Plus variables?