sqloracle-databaseplsqlodp.netora-01036

ORA-01036: illegal variable name/number on PL/SQL script


I'm fairly new to the PL/SQL game and I'm attempting to come up with a short script to create logins and grant read-only/read-write privs (making the necessary grants as necessary). However, I am encountering variable scoping issues with it. Can someone please help what I might be doing wrong?

Snipped is as follows:

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;

I'm doing the variable assignments using ODP.NET and parameterizing them using the OracleCommand.Parameters.Add()


Solution

  • All the DDL statements (GRANT, REVOKE, CREATE and ALTER) need to be in an EXECUTE IMMEDIATE such as

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

    and

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

    Actually, I'd simplify it a bit and put all the external variables in one place at the start.

    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;