Sleep timer problem Not working

M.bro wrote: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 fieldLike below scenarios1. FID -- Data receivedif 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 receivedif 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 receivedif 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;beginSELECT 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_L1SET ARD_STATUS = 'ard timeout', ARD_AC2_STATUS = 'ard timeout', ARD_TIMESTAMP = SYSDATE WHERE DECISION_STATUS = 'New' AND ARD_STATUS IS NULL; UPDATE EXP_L1SET 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.

We don't have your tables or data, so we can't run, test, or debug posted code.

See a picture of my car.

It doesn't work.

Tell me how to make my car go.

BTW - It is Worst Practice to store computed value in static table column.

Now I could be mistaken, but I believe the code could remain inside LOOP for around 1830 seconds; sum of 1,2,3,4,...,60)

Please click on URL below & respond with details for #6 - #9 inclusive.

Re: 2. How do I ask a question on the forums?

Too many things you don't explain what they are...

What I can't possibly know is what GET_VEP_COUNT does. Apparently it is some pipelined function, but who can tell what's behind that name.

If it's a pipelined function, then it looks quite strange to me the following:

SELECT FID, NPR, ARD_ID

        INTO V_COUNT_FID, V_COUNT_NPR, V_COUNT_ARD_ID

        FROM TABLE (VEP.GET_VEP_COUNT (P_ID));

and also

SELECT CARD_ID

INTO V_COUNT_CARD_ID

FROM TABLE (GET_VEP_COUNT (P_LANE_ID));

I say it looks strange because one would expect a pipelined function to return several records. And, if so, one would not select from a pipelined function into a single variable, like you do, for that will oftentimes raise exception too_many_rows.

Maybe it would make sense something like:

SELECT FID, NPR, ARD_ID

        INTO V_COUNT_FID, V_COUNT_NPR, V_COUNT_ARD_ID

        FROM TABLE (VEP.GET_VEP_COUNT (P_ID))

where rownum<=1;

That is you would select no more than one row. But, having only the information you gave, I can't be sure about anything.

One more thing is obvious to me: how do you handle the no_data_found exception? Because it may happen that that pipelined function returns no rows.

Therefore I can observe also no exception handling whatsoever.

John Thorton wrote:Now I could be mistaken, but I believe the code could remain inside LOOP for around 1830 seconds; sum of 1,2,3,4,...,60) 

You are mistaken - the variable passed to sleep is set to 1 and never changes.