473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query behaving differently in different times

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
Jun 14 '07 #1
4 1558
debasisdas
8,127 Expert 4TB
Hi
vishwadeepsharma
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.
Jun 14 '07 #2
debasisdas
8,127 Expert 4TB
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.
Jun 14 '07 #3
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_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 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
Jun 14 '07 #4
Debasis, below is the update

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 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.
Jun 14 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Patchwork | last post by:
Hi Everyone, Please take a look at the following (simple and fun) program: //////////////////////////////////////////////////////////////////////////// ///////////// // Monster Munch, example...
1
by: Jamie Burns | last post by:
Hello, I am writing a search engine that uses mysql to keep a word index. I have just added the "position" of each word, in relation to the document it is in. My table thus is as follows: ...
5
by: amit kumar | last post by:
I am calling a function which returns pointer to a map. The declaration of the map is map<int,vectxyz*>. vectxyz is a vector containing pointer to a class xyz. For map<int,vectxyz*>* p1 In the...
1
by: JD | last post by:
I'm using MS Access 97 in an XP environment. I have an append query at the top of a query tree that is behaving strangely. From design view, I can choose view datasheet - and I see one set of...
2
by: bingomanatee | last post by:
I have developed what amounts to a fancy shopping cart wizard for a scientific instrument using VB.NET. We are having some disturbing phenomena relating to dropdown controls. On my system and...
3
by: Sam Durai | last post by:
Here is a small testcase of the problem which I'm facing in prod env. db2 =describe table tab1 Column Type Type name schema name ...
5
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
8
by: fel | last post by:
does anybody know of any *web* query builder in PHP, something like the Access query builder, or similar, done in PHP or similar? I've looked for it trough the net, but nothing is colse to what I...
2
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.