我使用Oracle XE的唯一目的是开发程序,而版本11g显然已经丢失了GUI工具来管理10g曾经拥有的用户,所以我想准备一个代码片段来从命令行创建用户.我正在尝试
define variables所以我不需要输入相同的用户名16次,但我无法正确使用语法：
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;
/
06001
是否禁止在PL / sql块中使用CREATE USER语句或者我只是做了一个愚蠢的错字？是否必须使用sql * Plus变量？
PLS-00103: Encountered the symbol “CREATE” when expecting one of the following:
上述错误是因为您在PL / sql中使用DDL.你不能这样做.您必须(ab)使用EXECUTE IMMEDIATE在PL / sql中发出DDL语句.
例如,
sql> DECLARE 2 my_user VARCHAR2(30) := 'foo'; 3 my_password VARCHAR2(9) := '1234'; 4 BEGIN 5 EXECUTE IMMEDIATE 'CREATE USER '||my_user||' IDENTIFIED BY '||my_password; 6 EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO '||my_user; 7 END; 8 / PL/sql procedure successfully completed. sql> conn foo/1234@pdborcl Connected. sql> SHOW USER USER is "FOO"
从documentation快速参考,
Executing DDL and SCL Statements in PL/sql
Only dynamic sql can execute the following types of statements
within PL/sql program units:
Data definition language (DDL) statements such as
CREATE,DROP,GRANT,andREVOKESession control language (SCL) statements such as
ALTER SESSIONandSET ROLE- The
TABLEclause in theSELECTstatement
在旁注,
创建用户和授予权限通常是DBA负责的数据库管理任务.它不是通过PL / sql程序进行的频繁活动. DBA创建用户并将必要的权限授予一次性活动.
