Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Question Unanswered: Oracle ( 10G) giving bizarre results

    From past few days we are getting a very bizarre problem with one of our batch job.
    The details are-

    We get a file with tracking data in it.

    Our batch job processes the file and validates the data in file. First the data is loaded into worktables. Then it is validated against the LIVE tables.

    One of our check is behaving in very strange manner. It is marking the records as erroneous when it is very clear that the data is valid and they shouldn’t be marked as erroneous.

    The update which is working wrongly is-

    UPDATE wrk_tms_new_nap_trk wtnnt
    SET wtnnt.msg_cd = i_Error_Cd,
    wtnnt.msg_typ_cd = i_Error_Cat,
    wtnnt.upd_dt = SYSDATE,
    wtnnt.upd_user_id = i_Prg_Nm,
    wtnnt.chg_evnt_typ_cd = 'U'
    WHERE NOT EXISTS
    (SELECT 1
    FROM memberships mem,
    partner_lyty_scheme pls,
    loyalty_programmes lp
    WHERE mem.cid = wtnnt.cid
    AND mem.lyty_prg_cd = lp.lyty_prg_cd
    AND mem.curr_mbrp_sts_cd = 'A'
    AND lp.lyty_prg_typ_cd = 'F'
    AND mem.curr_mbrp_sts_start_dt <= TRUNC(SYSDATE)
    AND pls.partner_cd = wtnnt.partner_cd
    AND pls.lyty_scheme_cd = mem.lyty_prg_cd
    AND pls.start_dt <= TRUNC(SYSDATE)
    AND pls.end_dt >= TRUNC(SYSDATE)
    )
    AND wtnnt.msg_cd IS NULL
    AND wtnnt.prg_nm = i_Prg_Nm
    AND wtnnt.bch_no = i_Bch_No
    AND wtnnt.set_cd = i_Set_Cd ;

    The data in Live tables memberships, partner_lyty_scheme & loyalty_programmes is valid. Still it passes the NOT EXISTS check and marks the records as erroneous.

    This happens not with all the records in file. Say there are 10 records with similar data 7 fails and 3 succeed. The strange thing is when I reprocess the file even after 10 minutes without changing anything anywhere the previously error records does not fail and gets processed.

    For investigation I had requested DBAs to setup some triggers to capture the data of all the involved tables. The trigger captures the data of erroneous records and as expected all the things are valid. The sub select for these records brings back the rows.

    But somehow the ORACE at runtime decides that there are no rows returned from subselect. It passes the not exists check even though the row exists.

    This misbehaviour of Oracle has no trend. Sometime no files in entire day are affected and at time 3-4 files in same day are affected. I tried to observe a date/time pattern but this just happens anyday anytime.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    From what I can see you may have live tables that are changing during your loading by transactions which havent yet committed. So triggers wont pick this up as they will see the uncommitted data, the trigger wont record when the commit occurs and thus when the loading session will see the new data.


    Alan

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    114
    Hi Alan

    Thanks for the response.

    The LIVE tables are static ones and are not changed from ages.

    The live tables touched are-

    memberships mem,
    partner_lyty_scheme pls
    loyalty_programmes lp


    These are reference sort of tables which are not touched by any of the processes. The data in them is static from years.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Is wrk_tms_new_nap_trk wtnnt the only table altered by the loading process or are any of the tables in the update altered aswell?

    Alan

  5. #5
    Join Date
    Nov 2003
    Location
    India
    Posts
    114
    No other tables are altered.

    wrk_tms_new_nap_trk is the work table in which data is loaded and validated.

    All other tables are to get the data. None is altered.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would say the best thing to do if nothing else is altering the three tables in the subselect is to export the three tables in the subselect and the temp table wrk_tms_new_nap_trk before the next batch job. If the next run has an error import the four tables into your test database and run the update. Try duplicating the error its either a problem with your data or you've hit an Oracle internal bug (unlikely but it does happen). Check metalink for similar bugs.

    Alan
    Last edited by AlanP; 06-22-07 at 06:11.

  7. #7
    Join Date
    Nov 2003
    Location
    India
    Posts
    114
    The table wrk_tms_new_nap_trk remains empty. It only loads data when we get and load some file.

    I will give yesterday’s example regarding the data thing. We got a file with 10 records to process. The job marked 3 as success and 7 as errors. (6:45 PM)


    I ran the same job to process the same file. Ofcouse no data in any of the tables were altered. This time 7 records got processed and passed that check. ( 7:00 PM)

    As mentioned the data in all concerned LIVE tables is just like that from ages.

    I have tried to find the times/dates on which files falter but there is no definite trend.

    If we are suffering from Oracle bug is there any way to prove it..

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Like I said, export the four tables to a test system and reproduce the error. Load the staging table and then run the update statement. If it doesnt update properly you can check your data, subquery etc in isolation from the rest of your loading process. You also then have a test case to send to Oracle if you still cant find the problem.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •