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


I'm quite new to PL / SQL games and I'm trying to come up with a short script to create a login However, I am facing variables scoping issues with it, and grants only read / read-write private (by making the required grant necessary). Can someone make me wrong, can help please?

The snap is as follows:

  DECLARE CNT INTEGER; Select the CNT check COUNT (*) from the beginner dba_users where the user name =: ParamUserName IF (CNT> 0) then if (INSTR (: ParamSelectedRole, 'Write') = 0) then unlimited Tablespace cancellation: ParamUserName ; From REVOKE READ_WRITE: ParamUserName; Grant to READ_ONLY: Paramount name; Unlimited Table Facility for ELSE Grants: ParamUserName; end if; Other users create: ParamUserName Default tablespace identifies the temporary tablespace of users by temporary ": ParamUserPassword" PROFILE ELEV_USER; Optional user: parameter user password expired; Funding: ParamSelectedRole TO: ParamUserName; For this, approve session: ParamUserName; IF (INSTR (Parameters selected roll, Write ') & gt; 0) Allow unlimited Tacilia by: ParamUserName; end if; end if; End;   

I am working in variables using ODP.NET and parameterizing them using OracleCommand.Parameters.Add ()

All DDL statements (GRANT, revoke, create and ALTER) need to be in immediate execution as such a

  execute instant 'Revoxy Unlimited Taxes FoR' ||: Ultimate Username;   

and

  execute immediately 'ALTER USER' ||: ParamUserName || 'Password expiration';   

In fact, I want to make this a bit easier and initially put all the external variables in one place

  announcement V_CNT integer. V_USER VARCHAR2 (30); V_ROLE VARCHAR2 (30); BEGIN - V_USER: =: ParamUserName; V_ROLE: =: The best selected roll; V_PWD: =: ParamUserPassword; - Select COUNT (*) Username = v_user IF (CNT> 0) if V_CNT will be V_CNT from dba_users then (INSTR (v_role, 'Write') = 0) then instant execution || V_user 'unlimited table-space cancellation'; Take the conclusion 'REVOKE READ_WRITE' from || V_user; Execute immediate grant 'READ_ONLY TO' v_user; ELAS used the unlimited tabsis for immediate subsidy; V_user; end if; ELSE EXECUTE Immediate 'Create User' || V_user || 'Default tablespace users' temporary tablespace temp' || 'Recognized by' '|| V_pwd || ' Profile ELEV_USER '; Find out 'ALTER USER' || V_user || 'Password expired; Instant 'GRANT' || V_role executed || 'TO' || V_user; Executed instant || V_user 'Create for GRANT session'; (INSTR (v_role, 'Write')> 0) Then immediately for 'GRANT Unlimited Table-Space' || V_user executed; end if; end if; End;    

Comments