Joining two dblinks together

[ajilani1]

Hi team,

One of our clients has mentioned they are facing issues while trying to join the two dblinks together. They have created two dblinks and their use case involves joining the resultsets from both dblinks. But when they joing two dblinks they run into below error -

Utopia=> select * from (SELECT DBLINK(USING PARAMETERS cid='pgu', query='select node_name, node_address from nodes;') OVER()) g join (SELECT DBLINK(USING PARAMETERS cid='pscu', query='select node_name, node_address from nodes;') OVER()) sc Utopia-> on g.node_name=sc.node_name order by 1 desc; ERROR 8092: Failure in UDx RPC call InvokeProcessPartition() in User Defined Object [dblink]: UDx side process has exited abnormally Utopia=>

As of now, we have realized we cannot join two dblinks together without materializing. But for materialization we have to use with clause with the enable with clause materialization -

WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ g as ( SELECT DBLINK(USING PARAMETERS cid='sc', query='select node_name, node_address from nodes;') OVER() ), sc as ( SELECT DBLINK(USING PARAMETERS cid='pgu', query='select node_name, node_address from nodes;') OVER() ) select g.node_name, g.node_address, sc.node_address from g left join sc on g.node_name=sc.node_name;

Is there a way we can join two dblinks without materializing the resultset?