2084.execute immediate变量引号拼合及权限处理
在Oracle存储过程使用execute immediate语句建表时权限不足的解决方案
首先在存储过程中无法直接编译ddl语句，所以采用在execute immediate语句中执行ddl语句的方式
但是在存储过程中使用execute immediate执行ddl语句时，可能会报权限不足异常，如例：
create or replace procedure test_pro is
begin
execute immediate ‘create table bosdata.acct_apply_bak as select * from bosdata.acct_apply’;
end test_pro;
call test_pro();
1、authid current_user
首先在操作存储过程的用户下单独执行该ddl语句是有权限的，也就是说存储过程是没有权限的，所以让存储过程继承操作用户的权限就可以了
create or replace procedure test_pro authid current_user is
begin
execute immediate ‘create table bosdata.acct_apply_bak as select * from bosdata.acct_apply’;
end test_pro;
2、grant create any table to user;
但是在job定时任务中仍然无法执行成功
grant create any table to bosdata;
给用户赋予create any table权限之后就可以了
EXECUTE IMMEDIATE 存储过程中 权限不足及EXECUTE IMMEDIATE的调试避坑
原创 Oracle 作者：myis55555 时间：2019-09-26 16:15:55 1605 0
例子：
DECLARE
i_tblname VARCHAR2(100);
v_sql1 VARCHAR2(4000);
BEGIN
dbms_output.enable(9999999999);
v_sql1 :=
'insert into ‘||i_tblname||
’ select * from emploees where field = ‘‘xx’’;’
EXECUTE IMMEDIATE v_sql1;
dbms_output.put_line(v_sql1);
END;
/
– EXECUTE IMMEDIATE的调试
如果执行报错，解决思路可以将 v_sql1打印出来看下，是否符合执行标准。
负责的拼接语句很容易拼接出错，要及时调整。
输出sql查看时如果数据量太大可能会导致报错
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
在begin下加入 dbms_output.enable(9999999999);
设置 dbms_output输出的缓冲，默认是2000bytes！
– EXECUTE IMMEDIATE 在存储过程中 权限不足的修改方法 afterupdate:当前执行用户
grant
CREATE SESSION,
CREATE ANY TABLE,
CREATE ANY VIEW ,
CREATE ANY INDEX,
CREATE ANY PROCEDURE,
ALTER ANY TABLE,
ALTER ANY PROCEDURE,
DROP ANY TABLE,
DROP ANY VIEW,
DROP ANY INDEX,
DROP ANY PROCEDURE,
SELECT ANY TABLE,
INSERT ANY TABLE,
UPDATE ANY TABLE,
DELETE ANY TABLE
to afterupdate;
execute immediate中引号及变量的拼合处理
– 未结清业务客户
V_SQL:=‘select count(distinct(la.id_type||la.id_num)) as con
from (select t1.id_type,
t1.id_num,
t1.con_status,
ROW_NUMBER() OVER(PARTITION BY t1.contract_code ORDER BY t1.rpt_date DESC) as rn
from eqmea_creditrpt t1
where trunc(t1.msg_date, ‘‘dd’’) <= to_date(’’’||inptime||’’’, ‘‘yyyy-mm-dd’’)) la
where rn = 1
and la.con_status = ‘‘1’’’;
– 建表，需要create any table权限
str := ‘create table t_custinf_stat(信息记录类型 varchar2(100)
not null,
业务大类 varchar2(200),
未结清业务客户 number,
未报送基本信息 number)’;
execute immediate str;
DBMS_OUTPUT.PUT_LINE(V_SQL);
