通过PL/SQL块中的字符串变量创建用户 [英] Create user from string variables in a PL/SQL block

问题描述

我将Oracle XE用于开发PHP应用程序的唯一目的，而11g版本显然已经失去了管理10g曾经拥有的用户的GUI工具，因此我想准备一个代码段来从命令行创建用户.我正在尝试定义变量，因此我不需要键入相同的用户名16次，但语法不正确:

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

是禁止在PL/SQL块中使用CREATE USER语句，还是我只是做出了愚蠢的错字?是否必须使用SQL * Plus变量?

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?

推荐答案

PLS-00103:在预期以下情况之一时遇到符号"CREATE":

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

上述错误是由于您在 PL/SQL 中使用 DDL 而造成的.你不能这样做.您必须(ab)使用立即执行在 PL/SQL 中发布 DDL语句.

The above error is because of the fact that you are using DDL inside PL/SQL. You cannot do that. You must (ab)use EXECUTE IMMEDIATE to issue DDL statements in PL/SQL.

例如，

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"

来自 文档 ，

在PL/SQL中执行DDL和SCL语句

只有动态SQL 可以执行以下类型的语句 在PL/SQL程序单元中:

Only dynamic SQL can execute the following types of statements within PL/SQL program units:

• 数据定义语言(DDL)语句，例如CREATE，DROP，GRANT和REVOKE

• Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE

会话控制语言(SCL)语句，例如ALTER SESSION和SET ROLE

Session control language (SCL) statements such as ALTER SESSION and SET ROLE

在旁注，

创建用户和授予特权通常是DBA负责的数据库管理任务.它不是通过 PL/SQL 程序完成的频繁活动. DBA创建用户并授予必要的特权，这是一次性活动.

Creating users and granting privileges are usually database administration tasks taken care by the DBA. It is not a frequent activity done via PL/SQL program. DBA creates the users and grants the necessary privileges as a one time activity.

这篇关于通过PL/SQL块中的字符串变量创建用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！