时间戳

首页 > 解决方案 > Oracle PL/SQL 匿名块在修改为过程后停止工作

oracle - Oracle PL/SQL 匿名块在修改为过程后停止工作

问题描述

我写了一个可以正常工作的匿名块。SQL%ROWCOUNT 告诉我已经处理了 n 行：

declare
    user_list varchar2(100) := 'TESTUSER';
    v_test number;
begin
execute immediate '
    insert into config_all_objects (
    owner
    , object_name
    , object_type
    , created
    , status
    )
    select
    owner
    , object_name
    , object_type
    , created
    , status
    from all_objects
    where regexp_like(owner, ''^('||user_list||')$'', ''i'')'
    ;

    dbms_output.put_line(sql%rowcount);

end;
/

然后我把它改成存储过程，当我运行它时，已经处理了 0 行：

create or replace procedure p_test2(
    user_list varchar2
)
as
    v_test number;
    v_user_list varchar2(100) := user_list;
begin
execute immediate '
    insert into config_all_objects (
    owner
    , object_name
    , object_type
    , created
    , status
    )
    select
    owner
    , object_name
    , object_type
    , created
    , status
    from all_objects
    where regexp_like(owner, ''^('||v_user_list||')$'', ''i'')'
    ;

    dbms_output.put_line(sql%rowcount);

end;
/

begin p_test2 (user_list => 'TESTUSER');
end;
/

这是为什么？

我检查了两种情况下的立即执行命令是否完全相同。

此外，我确保用于运行这两个代码（eiauto）的用户在该表上具有直接授权......

grant select on all_objects to eiauto;

这是 config_all_objects 表的 DDL：

CREATE GLOBAL TEMPORARY TABLE CONFIG_ALL_OBJECTS
                ON COMMIT PRESERVE ROWS
                as select 
                owner
                , object_name
                , object_type
                , created
                , status
                from all_objects
                where 1=2

编辑：我真的无法回答这个问题，但我找到了一个解决方法：代替 ALL_OBJECTS 使用 DBA_OBJECTS 表，它工作正常。

这也让我想到了可能的原因。来自 Oracle 文档：

ALL_OBJECTS 描述了当前用户可以访问的所有对象。

DBA_OBJECTS 描述数据库中的所有对象。

我想这与特权有关...

标签： oraclestored-proceduresplsql

解决方案

我相信这是因为“ALL_”字典视图的性质而发生的。默认情况下，存储过程以“调用者的权限”运行。

AUTHID CURRENT_USER 子句可能会解决这个问题。

像这样的东西：

create or replace procedure p_test2(
    user_list varchar2
) AUTHID CURRENT_USER
as
var1 NUMBER;
begin
 --code here
end p_test2;

文档：https ://docs.oracle.com/cd/E11882_01/timesten.112/e21639/accesscntl.htm#TTPLS275

推荐阅读