Oracle Stored Procedure Sample

2flatu1s9h

　　
　　CREATE OR REPLACE PROCEDURE TEST
　　(
　　IN_BANK IN VARCHAR2,
　　IN_BROKERTYPE IN VARCHAR2,
　　IN_ISOESFORCELOGON IN VARCHAR2
　　)
　　AS
　　prodaltnum prod_alt_cde.prod_alt_num%TYPE;
　　var_midfsDownNo NUMBER(2):=-1;
　　var_fdsLinkStatusDownNo NUMBER(2):=-1;
　　var_fdsLinkStatusUpNo NUMBER(2):=-1;
　　var_oesLinkStatus VARCHAR2(1):='F';
　　var_timeout NUMBER(3):=300;
　　var_sleeptime NUMBER(2):=10;
　　var_sleeptimeTotal NUMBER(3):=0;
　　var_mktWatchStatusNoP NUMBER(2):=-1;
　　var_mktWatchStatusNoS NUMBER(2):=-1;
　　
　　exp_invalidBank EXCEPTION;
　　exp_midfs EXCEPTION;
　　exp_fds EXCEPTION;
　　exp_oesForceLogon EXCEPTION;
　　exp_stopFDSFailed EXCEPTION;
　　BEGIN
　　
　　---------------------------------------------
　　IF IN_BANK != 'HHHH' AND IN_BANK != 'SSSS' THEN
　　RAISE exp_invalidBank;
　　COMMIT;
　　END IF;
　　
　　---- STEP 0, enable MIDFS link, full price download for later order preparation
　　-- UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'N', SESS_LA_SEQ_NUM = -1;
　　-- COMMIT;
　　
　　-- var_sleeptimeTotal := 0;
　　-- WHILE var_midfsDownNo != 0 AND var_sleeptimeTotal < var_timeout LOOP
　　--   DBMS_LOCK.SLEEP(var_sleeptime);
　　--   var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;
　　--   SELECT Count(*) INTO var_midfsDownNo FROM MKT_DATA_FEED_STAT WHERE SESS_LA_SEQ_NUM = -1;
　　-- END LOOP;
　　
　　-- IF var_sleeptimeTotal >= var_timeout THEN
　　--   RAISE exp_midfs;
　　-- END IF;
　　
　　---- STEP 0, disable MIDFS link,
　　-- UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'Y';
　　-- COMMIT;
　　
　　---- STEP 0, reset Location
　　IF IN_BANK = 'HHHH' THEN
　　update MKT_DATA_FEED_STAT set LOC_PRC_SERVR_CDE=1 WHERE isnt_name in ('HKHHHHMF01','HKHHHHMF11','HKHHHHMF21','HKHHHHMF31','HKHHHHMF41','HKHHHHMF51');
　　update MKT_DATA_FEED_STAT set LOC_PRC_SERVR_CDE=2 WHERE isnt_name in ('HKHHHHMF02','HKHHHHMF12','HKHHHHMF22','HKHHHHMF32','HKHHHHMF42','HKHHHHMF52');
　　COMMIT;
　　ELSIF IN_BANK = 'SSSS' THEN
　　update MKT_DATA_FEED_STAT set LOC_PRC_SERVR_CDE=1 WHERE isnt_name in ('HKSSSSMF01','HKSSSSMF11','HKSSSSMF21','HKSSSSMF31','HKSSSSMF41','HKSSSSMF51');
　　update MKT_DATA_FEED_STAT set LOC_PRC_SERVR_CDE=2 WHERE isnt_name in ('HKSSSSMF02','HKSSSSMF12','HKSSSSMF22','HKSSSSMF32','HKSSSSMF42','HKSSSSMF52');
　　COMMIT;
　　END IF;
　　
　　-- Patch for both FDS/OES --
　　UPDATE RTP_SYS_PARM SET SYS_PARM_VALUE_TEXT = (SELECT TO_CHAR(SYS_AUTO_GENR_CURR_DT,'yyyyMMdd')
　　FROM RTP_EXCHG_CTRY_DT WHERE CTRY_PROD_EXCHG_MKT_CDE = 'HK')
　　WHERE SYS_PARM_NAME IN ('BATCH_COND_ORDER_RESEND_DATE','BATCH_UNCOND_ORDER_RESEND_DATE','LAST_ORDER_REPOSTING_DATE') AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';
　　COMMIT;
　　
　　---------------------------------------------
　　---- STEP 1, update SYS_PARM (update this if require OES. Reminded that TIG is also required in MR mode - send heartbeat to OES in order to maintain link status) <--- even if FDS only, should run this step also!!!!
　　UPDATE TRD_INTF_CTL
　　SET trd_ifc_ctl_prm_text = (SELECT to_char(sys_auto_genr_curr_dt,'yyyyMMdd') FROM RTP_EXCHG_CTRY_DT where CTRY_PROD_EXCHG_MKT_CDE = 'HK')
　　WHERE trd_ifc_ctl_prm_type = 'tryOnDate' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';
　　
　　UPDATE TRD_INTF_CTL
　　SET trd_ifc_ctl_prm_text = (SELECT to_char(sys_auto_genr_curr_dt - 10,'yyyyMMdd') FROM RTP_EXCHG_CTRY_DT where CTRY_PROD_EXCHG_MKT_CDE = 'HK')
　　WHERE trd_ifc_ctl_prm_type = 'tryOffDate' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';
　　COMMIT;
　　
　　---------------------------------------------
　　---- STEP 2, SQL for toggling percentage (HHHH ONLY)
　　-- verify
　　-- select * from DEAL_PARTY_REC_LOG_CNTL WHERE BROKER_CNTNG_MODE_CDE = 'N' AND GRP_SYS_PARM_CUST_CDE = 'PFS';
　　
　　IF IN_BANK = 'HHHH' THEN
　　-- update OES
　　UPDATE DEAL_PARTY_REC_LOG_CNTL SET DEAL_BROKER_SYS_PROC_PCT = 0
　　WHERE DEAL_PARTY_CDE = 'B01089'
　　AND BROKER_CNTNG_MODE_CDE = 'N'
　　AND GRP_SYS_PARM_CUST_CDE = 'PFS';
　　
　　-- update FDS
　　UPDATE DEAL_PARTY_REC_LOG_CNTL SET DEAL_BROKER_SYS_PROC_PCT = 100
　　WHERE DEAL_PARTY_CDE = 'B01490'
　　AND BROKER_CNTNG_MODE_CDE = 'N'
　　AND GRP_SYS_PARM_CUST_CDE = 'PFS';
　　
　　COMMIT;
　　END IF;
　　
　　---------------------------------------------
　　---- STEP 3, update Trading session on SUNDAY
　　UPDATE TRD_SESS SET TRD_SESS_START_TM = '50000', TRD_SESS_END_TM = '85959'
　　WHERE PROD_TYPE='LEQU' and TRD_SB_SESS_CDE='SESS01' and TRD_SESS_CDE='NORMAL' AND WKDY_TDY_CDE = 7;
　　
　　UPDATE TRD_SESS SET TRD_SESS_START_TM = '90000', TRD_SESS_END_TM = '230159'
　　WHERE PROD_TYPE='LEQU' and TRD_SB_SESS_CDE='SESS02' and TRD_SESS_CDE='NORMAL' AND WKDY_TDY_CDE = 7;
　　
　　UPDATE TRD_SESS SET TRD_SESS_START_TM = '230200', TRD_SESS_END_TM = '235959'
　　WHERE PROD_TYPE='LEQU' and TRD_SB_SESS_CDE='SESS03' and TRD_SESS_CDE='NORMAL' AND WKDY_TDY_CDE = 7;
　　
　　COMMIT;
　　
　　-- STEP3, Health check
　　-- select * from TRD_SESS where PROD_TYPE='LEQU' and TRD_SESS_CDE='NORMAL' AND WKDY_TDY_CDE = 7;
　　
　　---------------------------------------------
　　---- STEP 4, Enable OES Link status (update this if require OES.)
　　---- Reminded that TIG is also required in MR mode - send heartbeat to OES in order to maintain link status)
　　---- STEP 4: Method 1
　　IF IN_BROKERTYPE = 'OES' AND IN_ISOESFORCELOGON = 'NO' THEN
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'N' WHERE SYS_PROD_SUPLY_NUM='OES' AND TRD_IFC_CTL_PRM_TYPE='ctgIdc';
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'false' WHERE SYS_PROD_SUPLY_NUM='OES' AND TRD_IFC_CTL_PRM_TYPE='autoSwtOff';
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = '20130312160000' WHERE TRD_IFC_CTL_PRM_TYPE in ('shbDatTime','rhbDatTime') AND SYS_PROD_SUPLY_NUM = 'OES';
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'L' where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='linkStatus';
　　COMMIT;
　　
　　DBMS_OUTPUT.PUT_LINE('Enabled OES Link.');
　　---- STEP 4, Enable OES Link status HEALTH CHECK
　　-- select * from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='linkStatus';
　　-- select * from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='autoSwtOff';
　　-- select * from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE in ('shbDatTime','rhbDatTime');
　　-- SELECT * FROM TRD_INTF_CTL WHERE SYS_PROD_SUPLY_NUM = 'OES' AND TRD_IFC_CTL_PRM_TYPE ='ctgIdc';
　　
　　---- STEP 4: Method 2
　　---- LOGON OES using ForceLogon
　　---- **(don't apply to automated script)**
　　ELSIF IN_BROKERTYPE = 'OES' AND IN_ISOESFORCELOGON = 'YES' THEN
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'true' WHERE SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='forceLogon';
　　COMMIT;
　　
　　---- After patching to 'true', it triggered logon to OES automatically
　　var_sleeptimeTotal := 0;
　　WHILE var_oesLinkStatus != 'L' AND var_sleeptimeTotal < var_timeout LOOP
　　DBMS_LOCK.SLEEP(var_sleeptime);
　　var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;
　　SELECT TRD_IFC_CTL_PRM_TEXT INTO var_oesLinkStatus from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='linkStatus';
　　END LOOP;
　　
　　IF var_sleeptimeTotal >= var_timeout THEN
　　RAISE exp_oesForceLogon;
　　END IF;
　　
　　DBMS_OUTPUT.PUT_LINE('Enabled OES Link with ForceLogon.');
　　END IF;
　　
　　---------------------------------------------
　　---- STEP 5, Enable GFIX Link status (HHHH only)
　　IF IN_BANK = 'HHHH' AND IN_BROKERTYPE = 'FDS' THEN
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'L'
　　WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='linkStatus' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';
　　
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'N'
　　WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='ctgIdc' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';
　　
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'Y'
　　WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='ctgUpdSup' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';
　　
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'L'
　　WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='shdLnkStat' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';
　　
　　-- start socket
　　-- will become 'STARTED'
　　UPDATE TRD_INTF_GRP_CTL SET TRD_INTF_GRP_CTL_PRM_TEXT='START'
　　WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_INTF_GRP_CTL_PRM_NAME='appiaInstruction'
　　AND APP_INSTC_RUN_NAME LIKE 'HKHHHHMF%' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';
　　
　　COMMIT;
　　
　　var_sleeptimeTotal := 0;
　　WHILE var_fdsLinkStatusDownNo != 0 AND var_sleeptimeTotal < var_timeout LOOP
　　DBMS_LOCK.SLEEP(var_sleeptime);
　　var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;
　　SELECT Count(*) INTO var_fdsLinkStatusDownNo FROM TRD_INTF_GRP_CTL
　　WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_INTF_GRP_CTL_PRM_NAME='appiaInstruction' AND
　　APP_INSTC_RUN_NAME LIKE 'HKHHHHMF%' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK' AND TRD_INTF_GRP_CTL_PRM_TEXT!='STARTED';
　　END LOOP;
　　
　　IF var_sleeptimeTotal >= var_timeout THEN
　　RAISE exp_fds;
　　END IF;
　　
　　DBMS_OUTPUT.PUT_LINE('GFIX are all started');
　　END IF;
　　-- HEALTH CHECK
　　-- Select * from TRD_INTF_GRP_CTL  where sys_prod_suply_num='FDS' and trd_intf_grp_ctl_prm_name='appiaInstruction' and APP_INSTC_RUN_NAME like 'HKHHHHMF%' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK';
　　-- select * from trd_intf_ctl where sys_prod_suply_num='FDS' and trd_ifc_ctl_prm_type in ('linkStatus', 'ctgIdc', 'shdLnkStat','ctgUpdSup') and ctry_prod_exchg_mkt_cde='HK';
　　
　　---- STEP 6, Order preparation
　　prepareOrder(IN_BANK);
　　
　　---- STEP 7, Primary MDR full price download, will send out order to GFIX
　　UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'N', SESS_LA_SEQ_NUM = -1;
　　COMMIT;
　　
　　var_sleeptimeTotal := 0;
　　WHILE var_midfsDownNo != 0 AND var_sleeptimeTotal < var_timeout LOOP
　　DBMS_LOCK.SLEEP(var_sleeptime);
　　var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;
　　SELECT Count(*) INTO var_midfsDownNo FROM MKT_DATA_FEED_STAT WHERE SESS_LA_SEQ_NUM = -1;
　　END LOOP;
　　
　　IF var_sleeptimeTotal >= var_timeout THEN
　　RAISE exp_midfs;
　　END IF;
　　
　　
　　-- STEP 7, HEALTH CHECK (Expected result: RD_MKT_WTCH_STAT_CDE P -> S)
　　-- SELECT pa.prod_alt_num, tp.PROD_NUM, TRD_ORD_REFER_NUM, TRD_ORD_REFER_TYPE_CDE, PORTF_ORD_REFER_TYPE_CDE, PORTF_ORD_REFER_NUM, tp.REC_UPDT_LA_DT_TM, DEAL_PARTY_CDE, CTPT_RSULT_INPUT_CDE, ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatus
　　-- FROM trd_proc tp, prod_alt_cde pa
　　-- WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P'
　　-- ORDER BY  tp.REC_UPDT_LA_DT_TM DESC;
　　
　　var_sleeptimeTotal := 0;
　　WHILE var_mktWatchStatusNoP != 0 AND var_sleeptimeTotal < var_timeout LOOP
　　DBMS_LOCK.SLEEP(var_sleeptime);
　　var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;
　　SELECT Count(1) ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatusNoP FROM trd_proc tp, prod_alt_cde pa
　　WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P' AND ORD_MKT_WTCH_STAT_CDE = 'P';
　　END LOOP;
　　
　　SELECT Count(1) ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatusNoS FROM trd_proc tp, prod_alt_cde pa
　　WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P' AND ORD_MKT_WTCH_STAT_CDE = 'P';
　　IF var_sleeptimeTotal >= var_timeout THEN
　　DBMS_OUTPUT.PUT_LINE('Sent orders seccessfully: ' || var_mktWatchStatusNoS);
　　DBMS_OUTPUT.PUT_LINE('Sent orders failed: ' || var_mktWatchStatusNoP);
　　ELSE
　　DBMS_OUTPUT.PUT_LINE('All orders have been sent out. Totally: ' || var_mktWatchStatusNoS);
　　END IF;
　　
　　-- STEP 7, STOP Primary MDR MIDFS
　　UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'Y';
　　COMMIT;
　　
　　---- STEP 8, Order preparation
　　prepareOrder(IN_BANK);
　　
　　--- STEP 9 MDR failover
　　IF IN_BANK = 'HHHH' THEN
　　UPDATE MKT_DATA_FEED_STAT SET LOC_PRC_SERVR_CDE=1 WHERE ISNT_NAME IN ('HKHHHHMF02','HKHHHHMF12','HKHHHHMF22','HKHHHHMF32','HKHHHHMF42','HKHHHHMF52');
　　UPDATE MKT_DATA_FEED_STAT SET LOC_PRC_SERVR_CDE=2 WHERE ISNT_NAME IN ('HKHHHHMF01','HKHHHHMF11','HKHHHHMF21','HKHHHHMF31','HKHHHHMF41','HKHHHHMF51');
　　COMMIT;
　　ELSIF IN_BANK = 'SSSS' THEN
　　UPDATE MKT_DATA_FEED_STAT SET LOC_PRC_SERVR_CDE=1 WHERE ISNT_NAME IN ('HKSSSSMF02','HKSSSSMF12','HKSSSSMF22','HKSSSSMF32','HKSSSSMF42','HKSSSSMF52');
　　UPDATE MKT_DATA_FEED_STAT SET LOC_PRC_SERVR_CDE=2 WHERE ISNT_NAME IN ('HKSSSSMF01','HKSSSSMF11','HKSSSSMF21','HKSSSSMF31','HKSSSSMF41','HKSSSSMF51');
　　COMMIT;
　　END IF;
　　
　　---- STEP 10, Secondary MDR full price download, will send out order to GFIX
　　UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'N', SESS_LA_SEQ_NUM = -1 ;
　　COMMIT;
　　
　　var_sleeptimeTotal := 0;
　　WHILE var_midfsDownNo != 0 AND var_sleeptimeTotal < var_timeout LOOP
　　DBMS_LOCK.SLEEP(var_sleeptime);
　　var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;
　　SELECT Count(*) INTO var_midfsDownNo FROM MKT_DATA_FEED_STAT WHERE SESS_LA_SEQ_NUM = -1;
　　END LOOP;
　　
　　IF var_sleeptimeTotal >= var_timeout THEN
　　RAISE exp_midfs;
　　END IF;
　　
　　var_sleeptimeTotal := 0;
　　WHILE var_mktWatchStatusNoP != 0 AND var_sleeptimeTotal < var_timeout LOOP
　　DBMS_LOCK.SLEEP(var_sleeptime);
　　var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;
　　SELECT Count(1) ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatusNoP FROM trd_proc tp, prod_alt_cde pa
　　WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P' AND ORD_MKT_WTCH_STAT_CDE = 'P';
　　END LOOP;
　　
　　SELECT Count(1) ORD_MKT_WTCH_STAT_CDE INTO var_mktWatchStatusNoS FROM trd_proc tp, prod_alt_cde pa
　　WHERE ORD_FORMT_RMRK_TEXT = '### TEST MOD6 UPGRADE PRIMARY ###' AND pa.prod_num = tp.prod_num AND pa.PROD_CDE_ALT_CLASS_CDE = 'P' AND ORD_MKT_WTCH_STAT_CDE = 'P';
　　IF var_sleeptimeTotal >= var_timeout THEN
　　DBMS_OUTPUT.PUT_LINE('Sent orders seccessfully: ' || var_mktWatchStatusNoS);
　　DBMS_OUTPUT.PUT_LINE('Sent orders failed: ' || var_mktWatchStatusNoP);
　　ELSE
　　DBMS_OUTPUT.PUT_LINE('All orders have been sent out. Totally: ' || var_mktWatchStatusNoS);
　　END IF;
　　
　　-- STEP 10, STOP Seconary MDR MIDFS
　　UPDATE MKT_DATA_FEED_STAT SET TRMT_DATA_FEED_IND = 'Y';
　　COMMIT;
　　
　　---- STEP 11 stop OES/GFIX
　　-- Stop OES (if enabled)
　　IF IN_BROKERTYPE = 'OES' AND IN_ISOESFORCELOGON = 'NO' THEN
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'F' WHERE SYS_PROD_SUPLY_NUM = 'OES' AND TRD_IFC_CTL_PRM_TYPE ='linkStatus';
　　COMMIT;
　　-- Better method is ask OES to trigger log-off
　　
　　---- LOGOFF OES using ForceLogon
　　ELSIF IN_BROKERTYPE = 'OES' AND IN_ISOESFORCELOGON = 'YES' THEN
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'false' WHERE SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='forceLogon';
　　COMMIT;
　　
　　---- After patching to 'true', it triggered logon to OES automatically
　　var_sleeptimeTotal := 0;
　　WHILE var_oesLinkStatus = 'L' AND var_sleeptimeTotal < var_timeout LOOP
　　DBMS_LOCK.SLEEP(var_sleeptime);
　　var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;
　　SELECT TRD_IFC_CTL_PRM_TEXT INTO var_oesLinkStatus from TRD_INTF_CTL where SYS_PROD_SUPLY_NUM = 'OES' and TRD_IFC_CTL_PRM_TYPE ='linkStatus';
　　END LOOP;
　　END IF;
　　
　　-- Stop GFIX (HHHH only)
　　-- will become 'STOPPED'
　　IF IN_BANK = 'HHHH' AND IN_BROKERTYPE = 'FDS' THEN
　　UPDATE TRD_INTF_GRP_CTL SET TRD_INTF_GRP_CTL_PRM_TEXT='STOP'
　　WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_INTF_GRP_CTL_PRM_NAME='appiaInstruction' AND APP_INSTC_RUN_NAME LIKE 'HKHHHHMF%';
　　COMMIT;
　　
　　var_sleeptimeTotal := 0;
　　WHILE var_fdsLinkStatusUpNo != 0 AND var_sleeptimeTotal < var_timeout LOOP
　　DBMS_LOCK.SLEEP(var_sleeptime);
　　var_sleeptimeTotal := var_sleeptimeTotal + var_sleeptime;
　　SELECT Count(*) INTO var_fdsLinkStatusUpNo FROM TRD_INTF_GRP_CTL
　　WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_INTF_GRP_CTL_PRM_NAME='appiaInstruction' AND
　　APP_INSTC_RUN_NAME LIKE 'HKHHHHMF%' AND CTRY_PROD_EXCHG_MKT_CDE = 'HK' AND TRD_INTF_GRP_CTL_PRM_TEXT!='STOPPED';
　　END LOOP;
　　
　　UPDATE TRD_INTF_CTL SET TRD_IFC_CTL_PRM_TEXT = 'F' WHERE SYS_PROD_SUPLY_NUM='FDS' AND TRD_IFC_CTL_PRM_TYPE='linkStatus';
　　COMMIT;
　　
　　IF var_sleeptimeTotal >= var_timeout THEN
　　RAISE exp_stopFDSFailed;
　　END IF;
　　END IF;
　　
　　EXCEPTION
　　WHEN exp_invalidBank THEN
　　DBMS_OUTPUT.PUT_LINE('Invalid bank: ' ||  IN_BANK);
　　WHEN exp_midfs THEN
　　DBMS_OUTPUT.PUT_LINE('Cannot connect to MIDFS server.');
　　WHEN exp_fds THEN
　　DBMS_OUTPUT.PUT_LINE('Cannot connect to gfix server.');
　　WHEN exp_oesForceLogon THEN
　　DBMS_OUTPUT.PUT_LINE('Cannot connect to OES with forceLogon mode.');
　　WHEN exp_stopFDSFailed THEN
　　DBMS_OUTPUT.PUT_LINE('Cannot stop FDS socket.');
　　when others then
　　DBMS_OUTPUT.PUT_LINE('Exception');
　　END;