Oracle Unable to read link attribute value which is remote database from previous page

Question

I have an IG with the following query for PL/SQL returning function body:

declare
L_sql varchar2(4000);
l_instead varchar2(100);
begin
l_sql := 'select * from sys.table_links@replace_link'; 
l_instead  := replace(l_sql,'replace_link',:Pl_LINK)   ; 
   return l_sql;
   end;

Here P1 Link has its attribute copied from page 2. Page 2 has a column which is a link and it passes DB_LINK column value to item P1_LINK. However, when i try to validate the above code, it says remote database not found.

But the remote db link is working fine.

Answer 1

The issue is that the apex engine will try to validate the statement when the page is compiled. That is needed to determine a number of attributes of the report (eg nr of columns, data type of columns, etc). When the page is compiled, the value of :P1_LINK is null and the sql statement will be invalid.

Solution: change the sql statement so it always returns rows by adding an NVL or a union to a local table. Here is a similar question that makes this clear.