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.
It happened when I made a private DB link and got the ora-00942 error.
The user of the link is not the owner of the tables, but he is granted to access the tables
So, I changed the link to a public link - then everything worked fine.
精彩评论