• <tfoot id='hqt6J'></tfoot>

      <bdo id='hqt6J'></bdo><ul id='hqt6J'></ul>
  • <small id='hqt6J'></small><noframes id='hqt6J'>

    <i id='hqt6J'><tr id='hqt6J'><dt id='hqt6J'><q id='hqt6J'><span id='hqt6J'><b id='hqt6J'><form id='hqt6J'><ins id='hqt6J'></ins><ul id='hqt6J'></ul><sub id='hqt6J'></sub></form><legend id='hqt6J'></legend><bdo id='hqt6J'><pre id='hqt6J'><center id='hqt6J'></center></pre></bdo></b><th id='hqt6J'></th></span></q></dt></tr></i><div id='hqt6J'><tfoot id='hqt6J'></tfoot><dl id='hqt6J'><fieldset id='hqt6J'></fieldset></dl></div>
  • <legend id='hqt6J'><style id='hqt6J'><dir id='hqt6J'><q id='hqt6J'></q></dir></style></legend>

        从 PL/SQL 块中的字符串变量创建用户

        Create user from string variables in a PL/SQL block(从 PL/SQL 块中的字符串变量创建用户)
        • <i id='3UPgj'><tr id='3UPgj'><dt id='3UPgj'><q id='3UPgj'><span id='3UPgj'><b id='3UPgj'><form id='3UPgj'><ins id='3UPgj'></ins><ul id='3UPgj'></ul><sub id='3UPgj'></sub></form><legend id='3UPgj'></legend><bdo id='3UPgj'><pre id='3UPgj'><center id='3UPgj'></center></pre></bdo></b><th id='3UPgj'></th></span></q></dt></tr></i><div id='3UPgj'><tfoot id='3UPgj'></tfoot><dl id='3UPgj'><fieldset id='3UPgj'></fieldset></dl></div>
            <tbody id='3UPgj'></tbody>

          <small id='3UPgj'></small><noframes id='3UPgj'>

          • <bdo id='3UPgj'></bdo><ul id='3UPgj'></ul>
                  <tfoot id='3UPgj'></tfoot>

                  <legend id='3UPgj'><style id='3UPgj'><dir id='3UPgj'><q id='3UPgj'></q></dir></style></legend>

                  本文介绍了从 PL/SQL 块中的字符串变量创建用户的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

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

                  DECLAREmy_user VARCHAR2(30) := 'foo';my_password VARCHAR2(9) := '1234';开始CREATE USER my_user IDENTIFIED BY my_password DEFAULT TABLESPACE 用户;授予连接,资源给 my_user;授予 my_user 的创建数据库链接;向 my_user 授予创建物化视图;向 my_user 授予创建程序;向 my_user 授予创建公共同义词;授予 my_user 创建角色;向 my_user 授予创建序列;向 my_user 授予创建同义词;向 my_user 授予创建表;向 my_user 授予创建触发器;向 my_user 授予创建类型;向 my_user 授予创建视图;授予 SELECT_CATALOG_ROLE 给 my_user;GRANT SELECT ANY DICTIONARY to my_user;结尾;/

                  <块引用>

                   CREATE USER my_user IDENTIFIED BY my_password DEFAULT TABLESPACE 用户;*错误在第 5 行:ORA-06550:第 5 行,第 2 列:PLS-00103:在预期以下情况之一时遇到符号CREATE":(如果循环 mod null pragma,则开始 case 为 goto 声明退出raise return select update while with <an identifier><双引号分隔标识符><绑定变量><<继续关闭当前删除获取锁定插入打开回滚savepoint set sql 执行 commit forall 合并管道清除

                  是否禁止在 PL/SQL 块中使用 CREATE USER 语句,或者我只是犯了一个愚蠢的错字?是否必须使用 SQL*Plus 变量?

                  解决方案

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

                  上述错误是因为您在 PL/SQL 中使用了 DDL.你不能这样做.您必须 (ab) 使用 EXECUTE IMMEDIATEPL/SQL 中发出 DDL 语句.

                  例如

                  SQL>宣布2 my_user VARCHAR2(30) := 'foo';3 my_password VARCHAR2(9) := '1234';4 开始5 EXECUTE IMMEDIATE '创建用户'||my_user||'由'||my_password 识别;6 EXECUTE IMMEDIATE 'GRANT CREATE SESSION to '||my_user;7 结束;8/PL/SQL 过程成功完成.SQL>连接 foo/1234@pdborcl连接的.SQL>显示用户用户是FOO"

                  来自文档的快速参考,

                  <块引用>

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

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

                  • 数据定义语言 (DDL) 语句,例如 CREATEDROPGRANT撤销

                  • 会话控制语言 (SCL) 语句,例如 ALTER SESSIONSET ROLE

                  • SELECT 语句中的 TABLE 子句

                  顺便提一下,

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

                  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?

                  解决方案

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

                  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.

                  For example,

                  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"
                  

                  Quick reference from 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, and REVOKE

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

                  • The TABLE clause in the SELECT statement

                  On a side note,

                  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 块中的字符串变量创建用户的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?(如何将 DBMS_OUTPUT.PUT_LINE 的输出重定向到文件?)
                  How do I get column datatype in Oracle with PL-SQL with low privileges?(如何使用低权限的 PL-SQL 在 Oracle 中获取列数据类型?)
                  Get a list of all functions and procedures in an Oracle database(获取 Oracle 数据库中所有函数和过程的列表)
                  Why cannot I create triggers on objects owned by SYS?(为什么我不能在 SYS 拥有的对象上创建触发器?)
                  Returning result even for elements in IN list that don#39;t exist in table(即使对于表中不存在的 IN 列表中的元素也返回结果)
                  Reset Sequence in oracle 11g(oracle 11g 中的重置序列)

                  <legend id='LTKuk'><style id='LTKuk'><dir id='LTKuk'><q id='LTKuk'></q></dir></style></legend>
                • <small id='LTKuk'></small><noframes id='LTKuk'>

                    <tfoot id='LTKuk'></tfoot>
                    • <bdo id='LTKuk'></bdo><ul id='LTKuk'></ul>
                        <i id='LTKuk'><tr id='LTKuk'><dt id='LTKuk'><q id='LTKuk'><span id='LTKuk'><b id='LTKuk'><form id='LTKuk'><ins id='LTKuk'></ins><ul id='LTKuk'></ul><sub id='LTKuk'></sub></form><legend id='LTKuk'></legend><bdo id='LTKuk'><pre id='LTKuk'><center id='LTKuk'></center></pre></bdo></b><th id='LTKuk'></th></span></q></dt></tr></i><div id='LTKuk'><tfoot id='LTKuk'></tfoot><dl id='LTKuk'><fieldset id='LTKuk'></fieldset></dl></div>
                            <tbody id='LTKuk'></tbody>