Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Sleep timer problem Not working

M.broNov 18 2018 — edited Nov 21 2018

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.

Comments

Processing

Post Details

Added on Nov 18 2018
10 comments
416 views