oracle - PL/SQL insufficient privileges within a query manually possible

Question

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

This table has 2 columns :

FOO and BAR

I have FOO as PK and i know those values, they are both numbers but i don't have any value at 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;

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?

Answer 1

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.

While testing the PL/SQL queries set in advance

 set role none;

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