To create PL/SQL package with 2 procedure to create dynamic db_link based on parameters flag 0,1,2 and insert, update existing tables

Question

• .procedure to create and drop dblink( flag)

  if passing 0 then create

  if passing 1 then check

  if passing 2 then drop

execute immediate 'create database link DBLINK_' || cur.SYSTEM_NAME || ' connect to BOOWNER identified by b00wnerk0j using '''|| cur.IP_ADDRESS ||':1521/XE''';

dbms_output.put_line('select 1 from dual@DBLINK_'||cur.SYSTEM_NAME||'');

-- execute immediate 'select 1 from dual@DBLINK_'||cur.SYSTEM_NAME||'' into vData;

-- dbms_output.put_line(vData);

-- if (vData =1) then

DBMS_OUTPUT.put_line('Update executing..!');

else

-- DBMS_OUTPUT.put_line('Update not executing..!');

-- end if;

FUNCTION store_Validate_DBLINK(alink IN VARCHAR2)

RETURN NUMBER as

nCnt number;

vSql varchar2(32000);

BEGIN vSql := 'SELECT 1 FROM dual@'||alink; EXECUTE IMMEDIATE vSql into nCnt; return nCnt; exception when others then return 0;

END store_Validate_DBLINK;

2.procedure begin

for j in (select distinct loc_idnt from KOJ_CLOCKINOUT_MISSING t where status = 0 order by loc_idnt) loop call dblink procedure(0);

call dblink procedure(1); for i in (select * from KOJ_CLOCKINOUT_MISSING t where status = 0 and t.loc_idnt = j.loc_idnt order by loc_idnt, day_dt) loop

begin

execute immediate 'insert into kojobj.KOJ_CLOCKINOUT_PULLED

select id_em, ts_em_tm_enr, id_str_rt, rc_em_tm_enr,
typ_cd_tm_enr, mod_flag, ts_crt_rcrd, ts_mdf_rcrd,
dc_dy_bsn, sysdate TS_WH_UPD_RCRD, 0
WH_RCRD_PRCD_FLAG from boowner.co_em_tm_enr@DBLINK_MIK20082 where ( trunc(TO_DATE(TO_CHAR(yy.ts_em_tm_enr, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS')) = i.day_dt or to_date(yy.dc_dy_bsn, 'YYYY-MM-DD') = i.day_dt) and yy.id_str_rt = i.posstore ; '; end;

update KOJOBJ.KOJ_CLOCKINOUT_MISSING set status = 1 , comments =

'Data Pulled' -- if ststus = 0 data unavailable

where poststore= i.posstore and day_dt = i.dat_dt ;

end loop; call dblink procedure(2); -- dropping db_link at end

  end loop;

end;