Forum Stats

  • 3,839,092 Users
  • 2,262,450 Discussions
  • 7,900,854 Comments

Discussions

Sleep timer problem Not working

M.bro
M.bro Member Posts: 128 Blue Ribbon
edited Nov 21, 2018 4:43AM in SQL & PL/SQL

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.

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 18, 2018 9:38AM
    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?

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,834 Red Diamond
    edited Nov 18, 2018 11:52PM

    Anyone ever told you that Oracle is a multi-session, multi-user, and multi-transaction, processing platform?

    Because your "solution" seems to be oblivious of these facts.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy
    edited Nov 19, 2018 1:05AM

    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.

  • M.bro
    M.bro Member Posts: 128 Blue Ribbon
    edited Nov 19, 2018 11:36PM

    While insert new record in table i started the sleep function it should 10 seconds waiting for update the another column  mean while data while update particular column then sleep function will exit or update function happen will happen "Timeout" (Exceed 10 seconds). ?

    Now You understand how to make your car to go ?

  • M.bro
    M.bro Member Posts: 128 Blue Ribbon
    edited Nov 19, 2018 11:37PM

    "raise exception too_many_rows."

    Return only one value. Now can you explain my question if you know means ?

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 19, 2018 11:46PM
    M.bro wrote:While insert new record in table i started the sleep function it should 10 seconds waiting for update the another column mean while data while update particular column then sleep function will exit or update function happen will happen "Timeout" (Exceed 10 seconds). ?Now You understand how to make your car to go ?

    Sounds Great.

    So mark this thread as  ANSWERED.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Nov 20, 2018 4:05AM
    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.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Nov 20, 2018 4:09AM
    M.bro wrote:While insert new record in table i started the sleep function it should 10 seconds waiting for update the another column mean while data while update particular column then sleep function will exit or update function happen will happen "Timeout" (Exceed 10 seconds). ?Now You understand how to make your car to go ?

    You're going to have to explain what this code is supposed to do in a lot more detail.

    You're also going to have to explain what it's actually doing in a lot more detail.

    Probably with examples.

    That sentence above really doesn't mean anything to us - it looks like there are lots of words missing.

  • M.bro
    M.bro Member Posts: 128 Blue Ribbon
    edited Nov 20, 2018 11:17PM

    Below for simple script  : 

    create table TEST (EVENT_TI1 varchar2(15), EVENT_TI2 varchar2(15));

    insert into TEST (EVENT_TI1) values ('event 1 Received wating for event 2 data');

    EVENT_TI1 column Inserted waiting for EVENT_TI2  update statement maximum 5 second to update the  EVENT_TI2. if Data Is not received with in 5 second i will update the EVENT_TI2 Colum 'Timeout';

    ( or )

    insert into TEST (EVENT_TI2) values ('event 2 Received waiting for event 1 data');

    EVENT_TI2 column Inserted waiting for update statement maximum 20 second to update the EVENT_TI1. if Data Is not received with in 20 second i will update the EVENT_TI1 Column 'Timeout';

    So only i enabled the sleep function to wait for 5 seconds if COLUMN 2 - EVENT_TI2 OR 60 SECONDS.

    Note : BOTH THE DATA NOT RECEIVED SAME TIME. Either first column or second column.

    Thanks

    M.Bro.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Nov 21, 2018 4:43AM

    How on earth do you expect that script to be helpful?

    The code you posted does not refer to a table called test at any point, nor does it refer to columns event_ti1 or event_t2i.

    Not only that but it's rubbish in and of itself:

    SQL> create table TEST (EVENT_TI1 varchar2(15), EVENT_TI2 varchar2(15));Table createdSQL> insert into TEST (EVENT_TI1) values ('event 1 Received wating for event 2 data');insert into TEST (EVENT_TI1) values ('event 1 Received wating for event 2 data')ORA-12899: value too large for column "LIVE"."TEST"."EVENT_TI1" (actual: 40, maximum: 15)

    We ask people to post create table / insert scripts so we can recreate the data they have to come up with solutions to their problem.

    Your script is utterly useless for that in every conceivable way.

    It also helps to format code properly (and you should be properly formatting code every time you write it so you shouldn't need to do anything to it before posting here).

    Here's your code formatted:

    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) THEN --- Waiting for ard info        -- 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 LOOP MISSING HERE!!!!!!!>>>>>>>      END IF;END;

    I added that comment at the end. So what you've posted won't compile.

    It doesn't do any inserts at all.

    The only updates it does is on timeout.

    There is no attempt in the code you've posted to try and do the things you say it should be doing.

    You need to start again.

    You need to post the full correct code you currently have.

    You need to post the full correct code for the ref cursor you are calling as well.

    You need to post create table and insert scripts that are relevant to the problem and actually work.

    You need to describe in detail what you want the code to do.

    You need to describe in detail what the code is currently doing wrong.

    You need to pay attention to whether or not what you post actually makes any sense.