Oracle: "missing or invalid option" when trying to create user

Question

I have a table:

create table NHAN_VIEN (
    Ma_nhan_vien varchar2(5) not null,
    Ma_thu_vien char(3),
    Loai_nhan_vien char(3) not null,
    ...
    primary key (Ma_nhan_vien),
    constraint fk_NV_TV
    foreign key (Ma_thu_vien)
    references THU_VIEN(Ma_thu_vien),
    constraint fk_NV_LN
    foreign key (Loai_nhan_vien)
    references LOAI_NHAN_VIEN(Ma_loai_nhan_vien)
);

and a procedure:

CREATE OR REPLACE PROCEDURE HW31_CREATEUSER
AS
    CURSOR CUR IS (SELECT Ma_nhan_vien
                    FROM NHAN_VIEN
                    WHERE UPPER(Ma_nhan_vien) NOT IN (SELECT USERNAME
                                        FROM ALL_USERS));
    STRSQL VARCHAR(2000);
    USR VARCHAR2(5);
BEGIN
    OPEN CUR;
    STRSQL := 'ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE';
    EXECUTE IMMEDIATE(STRSQL);
    LOOP
        FETCH CUR INTO USR;
        EXIT WHEN CUR%NOTFOUND;
            
        STRSQL := 'CREATE USER '||USR||' IDENTIFIED BY '||USR;
        EXECUTE IMMEDIATE(STRSQL); -- These didn't work
        STRSQL := 'GRANT CONNECT TO '||USR;
        EXECUTE IMMEDIATE(STRSQL); -- These didn't work
    END LOOP;
    STRSQL := 'ALTER SESSION SET "_ORACLE_SCRIPT" = FALSE';
    EXECUTE IMMEDIATE(STRSQL);
    CLOSE CUR;
END;

I meant to create every user in NHAN_VIEN table. But when I exec HW31_CREATEUSER, my sqldeveloper said:

BEGIN HW31_CREATEUSER; END;
Error report -
ORA-00922: missing or invalid option
ORA-06512: at "SYS.HW31_CREATEUSER", line 18
ORA-06512: at line 1
00922. 00000 -  "missing or invalid option"

I can run my procedure if I delete the EXECUTE IMMEDIATE(STRSQL); Let me know if anything is unclear

Answer 1

You will need quotes around the relevant attributes (user/password) otherwise you can easily hit this error due to the data in your NHAN_VIEN source, eg

SQL> create user demo1 identified by abc;    -- abc is OK

User created.

SQL> create user demo2 identified by 9abc;   -- 9abc is not
create user demo2 identified by 9abc
                                 *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> create user demo2 identified by "9abc";  -- quotes to fix

User created.