execute immediate v_sql无权限问题

最新推荐文章于 2023-11-15 17:26:17 发布

Hehuyi_In

最新推荐文章于 2023-11-15 17:26:17 发布

阅读量1.8k

点赞数

分类专栏： Oracle 权限相关

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/94469377

版权

Oracle 同时被 2 个专栏收录

189 篇文章 38 订阅

订阅专栏

权限相关

12 篇文章 0 订阅

订阅专栏

创建一个简单同步数据的存储过程，在执行时总是报错

SQL> exec data_sync(p_table_name => 'test0701',p_begin_date => '2019-06-02 00:00:00',p_end_date   => '2019-06-03 00:00:00');
BEGIN data_sync(p_table_name => 'test0701',p_begin_date => '2019-06-02 00:00:00',p_end_date   => '2019-06-03 00:00:00'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "DATA_SYNC", line 32
ORA-06512: at line 1

需要执行的语句为

 v_sql := 'insert /*+ append parallel(ilmtest.' || p_table_name ||
           ',4) */ into ilmtest.' || p_table_name ||
           ' select /*+ parallel(i,4) */ * from ilmtest.' || p_table_name ||
           '@link i where i.msgtime>=' || v_begin_msgtime || ' and i.msgtime<' ||
           v_end_msgtime;

   dbms_output.put_line(v_sql);
   execute immediate v_sql;

生成的语句直接执行可以成功

SQL> insert /*+ append parallel(ilmtest.test0701,4) */ into ilmtest.test0701 select /*+ parallel(i,4) */ * from ilmtest.test0701@link i where i.msgtime>=1559318400000 and i.msgtime<1559404800000;

299 rows created.

Elapsed: 00:00:00.54

SQL> commit;

Commit complete.

查询文档发现 execute immediate 必须有显示的赋权，而用户的角色权限在这里不起作用。。。

给用户授权后发现执行成功，记录一下

SQL> grant insert on ilmtest.test0701 to testuser;

Grant succeeded.


SQL> exec data_sync(p_table_name => 'test0701',p_begin_date => '2019-06-02 00:00:00',p_end_date   => '2019-06-03 00:00:00');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.41
SQL> select count(*) from ilmtest.test0701;

  COUNT(*)
----------
      1317

参考

https://blog.csdn.net/royzhang7/article/details/51172413