在Oracle Procedure中立即执行 [英] Execute immediate within Oracle Procedure

问题描述

我有两个用户:SASDBA和CDM.模式SASDBA中有一个将对象保留在缓冲池中的过程.过程的一部分:

I have two users: SASDBA and CDM. There is a procedure in schema SASDBA which keeps objects into the buffer pool. Part of a procedure:

BEGIN
              FOR TABLE_ENTRY IN
              (SELECT          *
              FROM dba_tables
              WHERE owner = t_owner
              AND TABLE_NAME    = t_name_obj
              )
              LOOP
EXECUTE immediate 'alter table '||TABLE_ENTRY.OWNER||'.'||TABLE_ENTRY.TABLE_NAME||' storage (buffer_pool keep)';
    END LOOP;
END;

因此，如果我尝试将CDM.TABLE_TEST传递到过程中，则会出现错误:

So, if I try to pass CDM.TABLE_TEST into the procedure I get an error:

ORA-01031:特权不足

ORA-01031: insufficient privileges

但是，如果用户SASDBA对其进行更改而无需执行任何操作，那么它将起作用:

But, if user SASDBA alters it without procedure it works:

alter table CDM.TEST_TABLE  storage (buffer_pool keep)

表CDM.TEST_TABLE已更改.

table CDM.TEST_TABLE altered.

推荐答案

在PL/SQL块中，您只有直接授予您的特权.通过角色(例如 DBA )授予的特权不适用于PL/SQL块(即过程).

Inside PL/SQL blocks you have only privileges which are granted to you directly. Privileges which are granted through a role (e.g. DBA) do not apply inside a PL/SQL block, i.e. a procedure.

直接授予特权，例如 GRANT ALTER ANY TABLE TO SASDBA;

这篇关于在Oracle Procedure中立即执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！