Hi all,
I am using Oracle Database 11.0.1 Enterprise edition.
I have 3 Tables Like FID_LIVE, NPR_LIVE, ARD_LIVE. These 3 tables receiving data. Once receive the data from these three table based on the condition i will insert the get_vep_count table. If already records is there like using the fid_live data after receiving the other two table data i will just update the field
Like below scenarios
1. FID -- Data received
if there is no record in GET_VEP_COUNT table Insert operation (Other 2 Operation update(NPR,ARD In get_Vep_count)
2. NPR_live data received
if there is no record in GET_VEP_COUNT table Insert operation (Other 2 Operation update(FID,ARD In get_Vep_count)
3. ARD_live data received
if there is no record in GET_VEP_COUNT table Insert operation (Other 2 Operation update(NPR,FID In get_Vep_count)
Below code based on logic i return if there is no update for maximum 60 seconds i will update timeout option.
Declare
V_COUNT_FID NUMBER (1);
V_COUNT_NPR NUMBER (1);
V_COUNT_ARD_ID NUMBER (1);
-- Input Variables
V_RFID VEP_EXP_L1.FID%TYPE;
V_ANPR VEP_EXP_L1.NPR%TYPE;
V_CARD_ID VEP_EXP_L1.ARD_ID%TYPE;
-- Vaidity
V_VALID_STATUS VARCHAR2 (5);
-- Max Intervals
V_MAX_ARDTIME NUMBER (2) := 61;
V_MAX_FIDTIME NUMBER (2) := 6;
-- Sleep Timers
V_INC_TIMER NUMBER (2) := 0;
V_SLEEP_TIMER NUMBER (2) := 1;
begin
SELECT FID, NPR, ARD_ID
INTO V_COUNT_FID, V_COUNT_NPR, V_COUNT_ARD_ID
FROM TABLE (VEP.GET_VEP_COUNT (P_ID));
-- Check for further Inputs
IF (V_COUNT_FID = 1 AND V_COUNT_NPR = 0 AND V_COUNT_ARD_ID = 0) --- Waiting for ard info
THEN
-- 60 Seconds Wait Logic for ard info
LOOP
DBMS_LOCK.SLEEP (V_SLEEP_TIMER);
-- Check the Input
SELECT CARD_ID
INTO V_COUNT_CARD_ID
FROM TABLE (GET_VEP_COUNT (P_LANE_ID));
-- Timer
V_INC_TIMER := V_INC_TIMER + V_SLEEP_TIMER;
EXIT WHEN V_COUNT_CARD_ID = 1 OR V_INC_TIMER > V_MAX_ARDTIME;
--- ard input or / Time Out update
IF V_INC_TIMER = 60
THEN
UPDATE EXP_L1
SET ARD_STATUS = 'ard timeout',
ARD_AC2_STATUS = 'ard timeout',
ARD_TIMESTAMP = SYSDATE
WHERE DECISION_STATUS = 'New' AND ARD_STATUS IS NULL;
UPDATE EXP_L1
SET NPR_STATUS = 'Timeout',
NPR_TIMESTAMP = SYSDATE
WHERE DECISION_STATUS = 'New'
AND NPR_STATUS IS NULL;
END IF;
END IF;
End;
My problem is first records insertion is happen correctly but second table received any data need to update but it's not happen rather than it's insert other row. My condition one record only available in table once three records i updated i will delete the records form the get_v ep_count table. i used the sleep timer in above code once get the data from other 2 tables it's should update the records and terminate the sleep mode or else update the time out.
Thanks in advance.