PL/SQL 블록의 문자열 변수에서 사용자 생성 (Create user from string variables in a PL/SQL block)

문제 설명

PL/SQL 블록의 문자열 변수에서 사용자 생성 (Create user from string variables in a PL/SQL block)

저는 PHP 애플리케이션을 개발하는 유일한 목적으로 Oracle XE를 사용하고 있으며 버전 11g에서는 10g에 있던 사용자를 관리하기 위한 GUI 도구를 잃어버렸기 때문에 명령줄에서 사용자를 생성하기 위한 코드 스니펫을 준비하고 싶습니다. 변수를 정의하므로 동일한 사용자 이름을 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;
/

        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 변수를 반드시 사용해야 합니까?

참조 솔루션

방법 1:

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.

(by Álvaro González、Lalit Kumar B)

참조 문서

1. Create user from string variables in a PL/SQL block (CC BY‑SA 2.5/3.0/4.0)