如何在PL/SQL中用raise_application_error替代ORA-00942抛出权限异常

我有3个PL/SQL函数：

• Function1：通过user_tab_privs检查用户是否拥有表A、B、C的SELECT权限，统计无权限表的数量；
• Function2：从表A、B、C查询数据；
• Function3：判断Function1返回的无权限数量是否大于0，若大于0则抛出自定义异常，否则执行Function2并返回结果。

但当前用户权限缺失时，脚本直接抛出ORA-00942: table or view does not exist错误，而非我定义的异常。怎么才能让权限缺失时触发raise_application_error而不是原生的ORA-00942？

以下是Function3的代码片段：

function function3 return clob is
..
    BEGIN
        missingcount := function1();
       if missingcount >0 then
            error := 'Failed ' || missingcount || ' tables'; -- 修正原代码语法错误
            raise_application_error(-20101, error );
        ELSE
           fun2output := function2 ();
        end if;
    END function3;
BEGIN
 final := function3();
 dbms_output.put_line(final);
END;

问题根源 #

PL/SQL对静态SQL采用编译时绑定机制，Function2里直接写了对表A、B、C的静态查询，编译阶段Oracle就会检查这些表是否存在、当前用户是否有访问权限。如果权限缺失，编译Function2（或调用它的Function3）时就会直接抛出ORA-00942，根本轮不到Function1的权限检查逻辑执行。

解决步骤 #

1. 把Function2改成动态SQL #

用动态SQL（execute immediate）替代静态查询，这样Oracle只会在运行时解析SQL语句，绕过编译阶段的权限/对象存在性检查，让Function1的权限判断先生效。

修改后的Function2示例：

function function2 return clob is
    v_result clob;
begin
    -- 根据实际业务逻辑调整动态SQL内容
    execute immediate 'SELECT col1 || col2 FROM A UNION ALL SELECT col1 || col2 FROM B UNION ALL SELECT col1 || col2 FROM C'
    into v_result;
    return v_result;
exception
    -- 可选：捕获动态SQL执行时的权限错误，转为自定义异常（防止Function1漏检）
    when others then
        if sqlcode = -942 then
            raise_application_error(-20102, '访问表时权限不足或表不存在');
        else
            raise;
        end if;
end function2;

2. 给Function3添加全局异常捕获 #

即使Function1的权限检查有遗漏，也能在Function3里捕获ORA-00942并转为自定义异常，确保统一抛出我们定义的错误：

修改后的Function3示例：

function function3 return clob is
    missingcount number;
    error varchar2(200);
    fun2output clob;
begin
    missingcount := function1();
    if missingcount > 0 then
        error := 'Failed: ' || missingcount || ' tables lack SELECT permission';
        raise_application_error(-20101, error);
    else
        fun2output := function2();
    end if;
    return fun2output;
exception
    when others then
        -- 捕获ORA-00942，转为自定义异常
        if sqlcode = -942 then
            raise_application_error(-20103, '权限不足：无法访问目标表');
        else
            -- 其他异常原样抛出
            raise;
        end if;
end function3;

3. 完善Function1的权限检查逻辑 #

user_tab_privs仅显示直接授予用户的权限，角色赋予的权限需要额外查询。如果Function1漏检了角色权限，还是可能出现运行时错误，因此要补充角色权限的检查：

示例Function1的修正逻辑：

function function1 return number is
    v_count number;
begin
    -- 同时检查直接权限和角色赋予的权限
    select count(*)
    into v_count
    from (
        select table_name from user_tab_privs where privilege = 'SELECT' and table_name in ('A','B','C')
        union
        select tp.table_name from role_tab_privs tp
        join user_role_privs urp on tp.role = urp.granted_role
        where tp.privilege = 'SELECT' and tp.table_name in ('A','B','C')
    ) t;
    -- 返回无权限的表数量：总表数3减去有权限的数量
    return 3 - v_count;
end function1;

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