Hi Tom,
Hope you are doing good.

I got a requirement of getting a values from view that was built from 4 other views combined by UNION.Each 4 views referring and getting data from different DBlinks.So when a main view executed it still need to fetch the result from 3 other views even any one of them failed with DB Down/issue accessing DB link.

Create View V1 as select * from tb1@dblink1
Create View V2 as select * from tb1@dblink2
Create View V3 as select * from tb1@dblink3
Create View V4 as select * from tb1@dblink4

Create Mainview as
select * from V1
union
select * from V2
union
select * from V3
union
select * from V4

However i have written a function to get to know whether a particular db link is up or not by reading DUAL table.However i couldn't implement/append with view.Suppose if i combine with V1 as,
Create View V1 as select * from tb1@dblink1 where exists(select is_link_active('DBLINK1') from dual);
as it's giving me error. In other words,is there any way we can execute a query based on the result of a function return value?

function my_piped_function return my_nested_table_type pipelined is
begin
  -- run your check query against dual db1
  if test pass
  then
    for i in ( select * from v1 ) loop
      pipe row (  )
    end loop;
  end if;

  -- run your check query against dual db2
  if test pass
  then
    for i in ( select * from v2 ) loop
      pipe row (  )
    end loop;
  end if;

  -- run your check query against dual db3
  if test pass
  then
    for i in ( select * from v3 ) loop
      pipe row (  )
    end loop;
  end if;

  -- run your check query against dual db4
  if test pass
  then
    for i in ( select * from v4 ) loop
      pipe row (  )
    end loop;
  end if;
  return;

end;