手动可能无法在查询中使用PL/SQL特权不足

问题描述

我需要执行此任务，并更新具有很多行的表.

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

此表有2列:

FOO 和 BAR

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

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;

我已经在正确的 Test Environment 中测试了此功能，并且PL/SQL已创建并运行，但是当我要在Production中运行它时，我在Select wich put中有此错误 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特权不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！