Using DB Links on a PL/SQL script throws "Table not found" error

While trying to create a report using PL/SQL I'm finding myself stuck with the following problem, I created a SQL query that access 2 tables on a remote DB using a DB Link, running the query alone itself returns the expected results but when I run the very same query and put the results into a cursor I get a

PL/SQL: ORA-00942: table or view does not exist

error.

I'm not sure if it has something to do with the alias I'm using for each table or maybe the select statement is trying to select local tables, I don't know, do you have any suggestions?

PL/SQL:

DECLARE
   CURSOR t_bug_details IS (SELECT h.*
  FROM table1@REMOTEDB h,
       table2@REMOTEDB rml
  WHERE h.product_id   IN (1开发者_如何学运维23)
  AND h.category       IN ('category')
  AND h.status          < 4
  AND h.status NOT     IN (1,2,3)
  AND h.release_status IN (upper('P'))
  --AND h.programmer     IN (upper('MRFOO'))
  AND h.some_id      = rml.some_id
  and rownum <=400);

REPORT_DAY VARCHAR2(40);
mail_html clob;
mail_bod clob;

BEGIN

FOR v_some_details in t_bug_details
LOOP
REPORT_DAY := TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE(REPORT_DAY || '|' ||
                     v_some_details.reptnom || '|' ||
                     v_some_details.subject || '|' ||
                     v_some_details.field || '|' ||
                     v_some_details.release_status || '|' ||
                     v_some_details.status || '|' ||
                     v_some_details.category || '|' ||
                     v_some_details.sub_field || '|' ||
                     v_some_details.datef1 || '|' ||
                     v_some_details.field_by || '|' ||
                     v_some_details.programmer || '|' ||
                     TRUNC(sysdate - v_some_details.datef1) || '|' ||
                     TRUNC(sysdate - v_some_details.upd_date)|| '|' ||
                     v_some_details.fix_avail_date|| '|' ||
                     v_some_details.bug_type || '|' ||
                     v_some_details.base_reptnom);
END LOOP;

EXCEPTION WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE ('NO RECORDS FOUND');
END;

---

PL/SQL can run under different rights than SQL does. I am guessing you have rights on these tables granted through a role, which your user can use but PL/SQL can't by default. It is the difference between definer's rights and invoker's rights. See Oracle's documentation for more information.

---

I encounter the same error with 10g.

1. It happened when I made a private DB link and got the ora-00942 error.

2. The user of the link is not the owner of the tables, but he is granted to access the tables

3. So, I changed the link to a public link - then everything worked fine.