I have below for loop where i have select query inside this loop. I want to make this query dynamic by providing db links as a variable instead of harcoding it.
I also tried to put the complete select in variable but the looping is not working correctly and inserting come duplicate data later on in my table.
Is there any other way i can use db link dynamic in my select query and it will not affect the currently logic as its working fine ?
FOR IIS_DB IN C_DB
LOOP
IN_DB_LINK:=LEO_DB.DATABASE_LINK;
IN_DAY:=LEO_DB.DAY_ID;
FOR REC IN(
SELECT
order,dayiid,ety_id
from
(select t.dayiid from D_LOOP t where t.dayiid >= TO_NUMBER (TO_CHAR( TRUNC ( SYSDATE , 'IW' ) - 7 , 'YYYYMMDD' ))
and t.dayiid < TO_NUMBER ( TO_CHAR ( TRUNC ( SYSDATE , 'IW' ), 'YYYYMMDD' ))) TE,
(SELECT
count(DISTINCT o.order) nb_ord,
o.delete_dt,o.ety_id
FROM
PRP@tr_tp.com o,
PRP_TP@tr_tp.com m,
PRP_TT@tr_tp.com t,
PRP_TO@tr_tp.com t
WHERE
o.ord_status = 'CLO'
GROUP BY
o.delete_dt,o.ety_id
) ORD
WHERE
to_date(TE.dayiid,'yyyymmdd')=ORD.del_dt
and TE.dayiid=IN_DAY
ORDER BY
-- TE.market asc,
-- TE.entity asc,
TE.dayiid ASC)
loop