Oracle：含DBMS_RANDOM的动态SQL在存储过程中执行失败的原因

问题原因 #

核心差异在于PL/SQL匿名块与存储过程的权限生效规则不同：

• PL/SQL匿名块属于调用者权限，执行时会继承当前会话的所有权限（包括通过角色授予的权限）。
• 自定义存储过程默认采用定义者权限（即创建者的权限），但定义者权限不支持通过角色授予的权限——仅认可直接授予用户的权限。

如果你的DBMS_RANDOM执行权限是通过角色（比如CONNECT、RESOURCE或自定义角色）获得的，而非直接授予你的用户账号，就会出现匿名块正常、存储过程报错的情况。

底层机制解释 #

Oracle官方专家Tom Kyte对此的解释是：若允许定义者权限的PL/SQL对象使用角色权限，当角色权限被撤销时，所有依赖该角色的PL/SQL对象都会立即失效，需要耗时批量重新编译，这会给数据库带来巨大的性能和维护负担。因此Oracle在设计时限制了定义者权限对象对角色权限的支持。

解决方案 #

直接为用户授予DBMS_RANDOM的执行权限，而非通过角色：

GRANT EXECUTE ON SYS.DBMS_RANDOM TO your_username;

执行上述语句后，重新编译存储过程即可正常运行：

ALTER PROCEDURE TEST_DYNAMIC COMPILE;

复现步骤 #

1. 测试数据创建

SPOOL ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;

-- 创建测试表
CREATE TABLE TEST_DYNAMIC_TBL (
ID NUMBER PRIMARY KEY,
MY_COL VARCHAR2(50));

-- 插入数据并确认
INSERT INTO TEST_DYNAMIC_TBL VALUES(1, 'SOME TEXT'); 
COMMIT;
SELECT MY_COL FROM TEST_DYNAMIC_TBL;

2. PL/SQL匿名块（成功案例）

DECLARE
    l_script VARCHAR2 (32767);
BEGIN
    l_script := 'UPDATE TEST_DYNAMIC_TBL SET MY_COL = DBMS_RANDOM.STRING(''U'',5)';
    DBMS_OUTPUT.put_line ('执行的动态SQL: ' || l_script);
    EXECUTE IMMEDIATE l_script;
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line (' 错误: ' || SUBSTR (SQLERRM, 1, 64));
        ROLLBACK;
END;
/

-- 验证更新结果（执行成功）
SELECT MY_COL FROM TEST_DYNAMIC_TBL;

3. 存储过程（失败案例）

-- 创建逻辑完全相同的存储过程
CREATE OR REPLACE PROCEDURE TEST_DYNAMIC
AS
    l_script VARCHAR2 (32767);
BEGIN
    l_script := 'UPDATE TEST_DYNAMIC_TBL SET MY_COL = DBMS_RANDOM.STRING(''U'',5)';    
    DBMS_OUTPUT.put_line ('执行的动态SQL: ' || l_script);    -- 该SQL直接执行可成功
    EXECUTE IMMEDIATE l_script;
    COMMIT;

EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (' 错误: ' || SUBSTR (SQLERRM, 1, 64));
        ROLLBACK;
END;
/

-- 重置数据并确认
UPDATE TEST_DYNAMIC_TBL SET MY_COL = 'SOME TEXT'; 
COMMIT;
SELECT MY_COL FROM TEST_DYNAMIC_TBL;

-- 执行存储过程（会抛出ORA-00904错误）
EXECUTE TEST_DYNAMIC;

-- 检查表数据（未更新）
SELECT MY_COL FROM TEST_DYNAMIC_TBL;

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