ORA-01036: недопустимое имя / номер переменной в скрипте PL/SQL

Question

Я довольно новичок в игре PL/SQL, и я пытаюсь придумать короткий сценарий для создания логинов и предоставления привилегий только для чтения / чтения и записи (при необходимости делая необходимые гранты). Тем не менее, я сталкиваюсь с различными проблемами области видимости. Может кто-нибудь помочь, пожалуйста, что я могу делать не так?

Снято следующим образом:

DECLARE CNT INTEGER; 

BEGIN

  SELECT COUNT(*) 
    INTO CNT 
    FROM dba_users 
   WHERE username = :ParamUserName

  IF (CNT > 0) THEN
    IF (INSTR(:ParamSelectedRole, 'WRITE') = 0) THEN 
      REVOKE UNLIMITED TABLESPACE FROM :ParamUserName; 
      REVOKE READ_WRITE FROM :ParamUserName; 

      GRANT READ_ONLY TO :ParamUserName; 
    ELSE 
      GRANT UNLIMITED TABLESPACE TO :ParamUserName; 
    END IF; 
  ELSE 
    CREATE USER :ParamUserName DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP IDENTIFIED BY ":ParamUserPassword" PROFILE ELEV_USER; 
    ALTER USER :ParamUserName PASSWORD EXPIRE;

    GRANT :ParamSelectedRole TO :ParamUserName; 
    GRANT CREATE SESSION TO :ParamUserName; 

    IF (INSTR(:ParamSelectedRole, 'WRITE') > 0 ) THEN
      GRANT UNLIMITED TABLESPACE TO :ParamUserName; 
    END IF; 
  END IF;
END;

Я делаю назначения переменных с использованием ODP.NET и параметризации их с помощью OracleCommand.Parameters.Add()

Answer 1

Все операторы DDL (GRANT, REVOKE, CREATE и ALTER) должны быть в EXECUTE IMMEDIATE, например

EXECUTE IMMEDIATE 'REVOKE UNLIMITED TABLESPACE FROM '||:ParamUserName; 

а также

EXECUTE IMMEDIATE 'ALTER USER '||:ParamUserName||' PASSWORD EXPIRE';

На самом деле, я бы немного упростил и поместил все внешние переменные в одном месте в начале.

DECLARE 
   V_CNT INTEGER; 
   V_USER VARCHAR2(30);
   V_ROLE VARCHAR2(30);
BEGIN
  --
  V_USER := :ParamUserName;
  V_ROLE := :ParamSelectedRole;
  V_PWD  := :ParamUserPassword;
  --
  SELECT COUNT(*) 
    INTO V_CNT 
    FROM dba_users 
   WHERE username = v_user

  IF (CNT > 0) THEN
    IF (INSTR(v_role, 'WRITE') = 0) THEN 
      EXECUTE IMMEDIATE 'REVOKE UNLIMITED TABLESPACE FROM '||v_user; 
      EXECUTE IMMEDIATE 'REVOKE READ_WRITE FROM '||v_user; 
      EXECUTE IMMEDIATE 'GRANT READ_ONLY TO '||v_user; 
    ELSE 
      EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO '||v_user;
    END IF; 
  ELSE 
    EXECUTE IMMEDIATE 'CREATE USER '||v_user||
                      'DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP '||
                      'IDENTIFIED BY '||v_pwd||' PROFILE ELEV_USER'; 
    EXECUTE IMMEDIATE 'ALTER USER '||v_user||' PASSWORD EXPIRE;

    EXECUTE IMMEDIATE 'GRANT '||v_role||' TO '||v_user; 
    EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO '||v_user; 

    IF (INSTR(v_role, 'WRITE') > 0 ) THEN
      EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO '||v_user; 
    END IF; 
  END IF;
END;