Strange behavior: db links inside PL/SQL procedure

[DeepDiverMom]

This is the second time in as many weeks that I have encountered problems with database-link references within a PL/SQL block, while the problem does not exist at the SQL prompt.

Note, below, the successful DESCRIBE of the "submissions" table via the database link, "mss":

SQL> desc submissions@mss
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 SUBMSN_ID                                 NOT NULL NUMBER(7)
 UNIT_TYP_CD                               NOT NULL NUMBER(3)
 STATUS_FLAG                                        VARCHAR2(1)
 STS_CHG_DT                                         DATE
 CREATE_DT                                          DATE

Note, below, the successful access of the db-linked table from the SQL> prompt:

SQL> select status_flag from submissions@mss;

S
-
1
1
1
3
1
2
1

Now notice the syntax errors that occur when the same query becomes an implicit cursor:

SQL> begin
  2      for x in (select status_flag from submissions@mss) loop
  3          dbms_output.put_line(x.status_flag);
  4      end loop;
  5  end;
  6  /
    for x in (select status_flag from submissions@mss) loop
                    *
ERROR at line 2:
ORA-06550: line 2, column 21:
PL/SQL: ORA-00904: "STATUS_FLAG": invalid identifier
ORA-06550: line 2, column 14:
PL/SQL: SQL Statement ignored
ORA-06550: line 3, column 30:
PLS-00364: loop index variable 'X' use is invalid
ORA-06550: line 3, column 9:
PL/SQL: Statement ignored

I successfully create a VIEW (named "submissions_mss") of the db-linked table:

create or replace view submissions_mss as select * from submissions@mss;

View created.

And if, in the PL/SQL block, I refer to the VIEW of the db-linked table, instead of directly referring (as before) to the db-linked table, everything is fine:

set serveroutput on
begin
    for x in (select status_flag from submissions_mss) loop
        dbms_output.put_line(x.status_flag);
    end loop;
end;
/
1
1
1
3
1
2
1

PL/SQL procedure successfully completed.

Can someone explain this (mis)behavior?...and how to fix the behavior so that I don't have to use a VIEW?

 

[LKBrwnDBA]

We have experienced the same situation in 10g and it appears to be related to the procedure not being able to resolve the identifiers from the link at compilation and/or execution time. We beleive it's due to not knowing the permissions from the underlying tables (but this is speculation).

The work-around has allways been to create a view.

Maybe some guru out there can give us the real explanation?




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb