Procedure of updating_user throws error, when the parameter is null

Question

I have next function, that hashes user password before inserting data in table:

--/
CREATE OR REPLACE FUNCTION hash_password
(
    f_password IN CLOB
)
RETURN RAW
IS
    hash RAW(32);
BEGIN
    hash := dbms_crypto.hash(f_password, dbms_crypto.hash_sh256);
    RETURN hash;
END;
--/

And I have this procedure for updating user data:

--/
CREATE OR REPLACE PROCEDURE update_user
(
    p_id in USERS.id%TYPE,
    p_user_role in USERS.user_role%TYPE,
    p_email in USERS.email%TYPE,
    p_password in CLOB
)
IS
BEGIN
    UPDATE USERS
    SET user_role = (select nvl2(p_user_role, p_user_role, (select user_role from users where id = p_id)) from dual),
        email = (select nvl2(p_email, p_email, (select email from users where id = p_id)) from dual),
        password = (select nvl2(p_password, (select hash_password(p_password) from dual), (select password from users where id = p_id)) from dual)
    WHERE id = p_id;
    COMMIT;
END;
--/

Oracle throws me these kind of errors:

[Code: 1405, SQL State: 22002]  ORA-01405: fetched column value is NULL
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 159
ORA-06512: at "SYS.DBMS_CRYPTO", line 86
ORA-06512: at "SYSTEM.HASH_PASSWORD", line 9
ORA-06512: at "SYSTEM.UPDATE_USER", line 10
ORA-06512: at line 2
  [Script position: 17878 - 17883]

But WHY? I use nvl2 function, I thought that I predicted the situation when the parameter is null.

I have tried to create additional variable, that will contain old password. And if parameter is not null, then I change the value of this variable. But it did not help me :(

P.S. If you need the sctructure of my table, then here:

CREATE TABLE USERS
(
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_role NVARCHAR2(64) NOT NULL,
    email NVARCHAR2(256) NOT NULL UNIQUE,
    password RAW(32) NOT NULL,
    CONSTRAINT FK_USER_ROLE
        FOREIGN KEY (user_role)
        REFERENCES ROLES (role_name)
);

Answer 1

NVL, NVL2, DECODE, etc... these functions do not short-circuit evaluate (in fact, almost nothing in SQL does... if you want to avoid making a call using branching logic it typically requires PL/SQL). Oracle will resolve all their parameters first in any order it wants and then pass them into the function program. So your hash_password will execute every time, whether or not you have passed in a NULL for the password. That NULL being fed into the hash algorithm is what is raising your exception.

Address NULL values in your hash function. Also, don't use CLOB for passwords... dbms_crypto.hash supports RAW as well, which you can convert to from a much more reasonable varchar2 datatype. I also suggest simplifying your update statement to avoid those unnecessary subqueries. You can refer to the existing values in the same query block and use them with NVL:

CREATE OR REPLACE FUNCTION hash_password(f_password IN varchar2)
  RETURN raw
AS
BEGIN
  IF f_password IS NULL
  THEN
    RETURN NULL;
  ELSE
    RETURN sys.dbms_crypto.hash(utl_raw.cast_to_raw(f_password), sys.dbms_crypto.hash_sh256);
  END IF;
END;
/

CREATE OR REPLACE PROCEDURE update_user
(
    p_id in USERS.id%TYPE,
    p_user_role in USERS.user_role%TYPE,
    p_email in USERS.email%TYPE,
    p_password in varchar2
)
IS
BEGIN
  UPDATE USERS
     SET user_role = NVL(p_user_role, user_role),
         email = NVL(p_email, email),
         password = NVL(hash_password(p_password),password)
   WHERE id = p_id;
  
  COMMIT;
END;