参数为null时update_user存储过程抛出ORA-01405错误排查

编写了用于哈希用户密码的hash_password函数：

--/
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;
--/

同时编写了更新用户数据的update_user存储过程：

--/
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;
--/

当p_password参数为null时，Oracle抛出错误：

[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]

使用nvl2处理null参数无效，尝试用变量存储旧密码也没解决问题。

USERS表结构：

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)
);

错误原因 #

核心问题是Oracle函数的急切求值特性：nvl2会提前执行所有传入的表达式，不管条件是否成立。哪怕p_password是null，nvl2第二个参数里的hash_password(p_password)仍然会被调用。而dbms_crypto.hash不接受null输入，传入null时直接触发ORA-01405错误——它无法对null值进行哈希运算。

另外，UPDATE语句里嵌套子查询的写法完全多余，既降低执行效率，还容易引发这类求值顺序的问题。

解决方案 #

1. 给hash_password函数增加null参数判断，避免传入null到dbms_crypto.hash：

--/
CREATE OR REPLACE FUNCTION hash_password
(
    f_password IN CLOB
)
RETURN RAW
IS
    hash RAW(32);
BEGIN
    IF f_password IS NULL THEN
        RETURN NULL; -- 若业务不允许返回null，可根据需求调整，比如抛出自定义异常
    END IF;
    hash := dbms_crypto.hash(f_password, dbms_crypto.hash_sh256);
    RETURN hash;
END;
--/

2. 重构update_user存储过程，用短路求值的CASE语句替代nvl2，同时去掉冗余子查询：

--/
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 = COALESCE(p_user_role, user_role),
        email = COALESCE(p_email, email),
        password = CASE 
                      WHEN p_password IS NOT NULL THEN hash_password(p_password)
                      ELSE password
                   END
    WHERE id = p_id;
    COMMIT;
END;
--/

CASE是短路求值逻辑：只有当p_password不为null时，才会调用hash_password，否则直接使用原行的password值，从根源避免了null参数传入哈希函数的情况。COALESCE则可以直接替换原来的嵌套子查询，简洁实现“参数非空则用参数，否则保留原值”的逻辑。

内容的提问来源于stack exchange，提问作者0ero_1ne