Query behaving differently in different times

[vishwadeepsharma]

Hi Freinds,
We are facing a very strange problem. Our system consists of UNIX batches which call the PLSQL procedures to execute the validations on the database.
We have a list of plsql procedure names stored in a table with a column having a sequence in which they have to be executed.

The problem is that one of the procedure is working very differently. Some times when we execute the batch it gives the error on one record, and if we execute it again it processes the record successfully. We have recently upgraded to oracle 10g dont know if this is related to that, but its causing lots of confusion.

Has anyone experienced such a problem when a simple update behaves differently for the same data on different times.
plz help

[debasisdas]

Hi
vishwadeepsharm a
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow the posting guidelines in every new post/reply.

Please specify your post/question clearly.

Dont expect others to guess and solve your problem.

[debasisdas]

Is there any dependency of the base table with other tables.

It will never happen that the query will behaving differently in different times

There must be some error in your logic.

[vishwadeepsharma]

Is there any dependency of the base table with other tables.

It will never happen that the query will behaving differently in different times

There must be some error in your logic.

Debasis, below is the update

UPDATE wrk_tms_new_nap _trk wtnnt
SET wtnnt.msg_cd = i_Error_Cd,
wtnnt.msg_typ_c d = 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_sc heme pls,
loyalty_program mes lp
WHERE mem.cid = wtnnt.cid
AND mem.lyty_prg_cd = lp.lyty_prg_cd
AND mem.curr_mbrp_s ts_cd = 'A'
AND lp.lyty_prg_typ _cd = 'F'
AND mem.curr_mbrp_s ts_start_dt <= TRUNC(SYSDATE)
AND pls.partner_cd = wtnnt.partner_c d
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 values are picked up from a file, and the select returns data when the records are checked later but the update still marks the error. Can you figure out some possible resons for this...thx

[vishwadeepsharma]

Debasis, below is the update

UPDATE wrk_tms_new_nap _trk wtnnt
SET wtnnt.msg_cd = i_Error_Cd,
wtnnt.msg_typ_c d = 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_sc heme pls,
loyalty_program mes lp
WHERE mem.cid = wtnnt.cid
AND mem.lyty_prg_cd = lp.lyty_prg_cd
AND mem.curr_mbrp_s ts_cd = 'A'
AND lp.lyty_prg_typ _cd = 'F'
AND mem.curr_mbrp_s ts_start_dt <= TRUNC(SYSDATE)
AND pls.partner_cd = wtnnt.partner_c d
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 values are picked up from a file, and the select returns data when the records are checked later but the update still marks the error. Can you figure out some possible resons for this...thx

Is it possible that oracle is rewriting the query and writing it wrong.