Hi all,
I want to create a view with results depending by a table TABLE1
and table TABLE2.
A dblink exists retrievind data from TABLE2.
The problem I have is that some customers may do not have the
db link and I do not want to retrieve data from TABLE2 for them.
CREATE OR REPLACE FORCE EDITIONABLE VIEW TEST_VIEW
SELECT A1,
A2
FROM TABLE1
UNION
SELECT A1,
A2
FROM TABLE2@DBLINK_NAME;
Is there any way to make the union only if the db link exists ?
Moreover, I would like to retrieve all the views which are based on the above db link
in order to do the same modifications
WITH TBLA AS (
SELECT DBMS_METADATA.GET_DDL('VIEW',a.view_name) as cmd
FROM ALL_VIEWS a
WHERE a.owner = 'OWNER1'
)
SELECT *
FROM TBLA
WHERE INSTR(TBLA.CMD,'DBLINK_NAME',1)<> 0 ;
Thank you in advance