PL/SQL 在手动查询中权限不足 [英] PL/SQL insufficient privileges within a query manually possible

问题描述

我需要完成这个任务，并更新这个有很多行的表.

I need to do this task , and update this table that has a lot of rows.

此表有 2 列:

FOO 和 BAR

我有 FOO 作为 PK，我知道这些值，它们都是数字，但我在 bar 上没有任何值.

I have FOO as PK and i know those values, they are both numbers but i don't have any value at bar.

我可以毫无问题地手动运行每个查询，但是我制作了这个 PL/SQL，因此一旦我需要在另一个查询中找到 BAR 值，它就会自动运行而没有任何问题.

I can manually run every query without any problems, but i made this PL/SQL so it automatically run without any problem, once i need to find the BAR value within another query.

create or replace procedure FxB_pro
IS
  tmpFIELD NUMBER := 0; 
  i NUMBER := 0;
  cursor c1 is  
    SELECT * FROM FooXBar WHERE BAR IS NULL;
BEGIN

  FOR CTUpdate IN c1
  LOOP 
    BEGIN
      SELECT t5.bar INTO tmpFIELD FROM table_1 t1, table_2 t2, table_3 t3, table_4 t4, table_5 t5, table_6 t6
      where t1.fielda_id = t2.fielda_id
      and t2.fielda_id = t3.fielda_id
      and t3.fieldb_id = t4.fieldb_id 
      and t3.fieldb_id = t6.fieldb_id
      and t4.fieldd_id = t5.fieldc_id
      and t1.fieldc = CTUpdate.FOO
      and rownum = 1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        tmpFIELD :=null;
    END;

    UPDATE FooXBar set BAR = tmpFIELD where FOO=CTUpdate.FOO;

    i := i+1;

    IF mod(i, 1000) = 0 THEN    -- Commit every 1000 records
      COMMIT;
    END IF;

  END LOOP; 
  COMMIT;
END;

我已经在我正确的测试环境中对此进行了测试，PL/SQL 已创建并运行，但是当我要在生产中运行它时，我在 Select 中遇到了这个错误tmpFIELD 中的值:

I've tested this in my properly Test Environment the PL/SQL Is created and runs, but when i'm going to run it in Production , i have this error in the Select wich put the value in tmpFIELD :

Erro(12,11): PL/SQL: SQL Statement ignored
Erro(12,143): PL/SQL: ORA-01031: insufficient privileges

我不明白为什么会这样，有人可以帮我吗?

I can't figure why this is happening, can someone please help me?

推荐答案

您的权限是通过 ROLE 分配的.这适用于直接 SQL，但不适用于 PL/SQL.

Your privileges are assigned via ROLE. This is fine with direct SQL, but don't work with PL/SQL.

您需要直接为您的用户获取权限.

You need to acquire the privileges direct to you user.

同时测试预先设置的 PL/SQL 查询

While testing the PL/SQL queries set in advance

 set role none;

这将停用通过 ROLE 获得的权限，并显示在 PL/SQL 中运行时可能出现的问题.

this will deactivate the priviledges acquired via ROLE and show possible problems running in PL/SQL.

这篇关于PL/SQL 在手动查询中权限不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！