Home » SQL & PL/SQL » SQL & PL/SQL » Query not execute for the first time
Query not execute for the first time [message #353222] Mon, 13 October 2008 00:10 Go to next message
senaka
Messages: 5
Registered: September 2008
Junior Member
Hi can someone help to identify the strange behavior in my Oracle 9.2.0.2.0 database.

When I execute the following query from the appowner it fetches me the correct result.

select COMPANY , ACCOUNTING_YEAR , ACCOUNTING_PERIOD, ACCOUNT, SIMULATION_VOUCHER,
sum(AMOUNT_BALANCE) ,
sum(DEBET_BALANCE),
sum(CREDIT_BALANCE),
sum(CURR_AMOUNT_BALANCE),
sum(CURR_DEBET_BALANCE),
sum(CURR_CREDIT_BALANCE),
sum(QTY_BALANCE),
sum(THIRD_CURRENCY_DEBIT_BALANCE),
sum(THIRD_CURRENCY_CREDIT_BALANCE),
sum(THIRD_CURRENCY_AMOUNT_BALANCE)
from ifsapp.ACCOUNTING_BALANCE_AUTH
where COMPANY = '01' and ( SIMULATION_VOUCHER = 'FALSE' ) and (ACCOUNTING_YEAR = 2008 and ACCOUNTING_PERIOD between 1 and 9 and ACCOUNT = '5010' and CODE_B = '495')
group by COMPANY , ACCOUNTING_YEAR , ACCOUNTING_PERIOD, ACCOUNT, SIMULATION_VOUCHER
order by COMPANY , ACCOUNTING_YEAR , ACCOUNTING_PERIOD, ACCOUNT, SIMULATION_VOUCHER

But when I execute this from the end user, the query gives me a result which is no where in the database. But when I execute this for the second time (subsequent attempt) then the query gives me the correct result.

Sometimes the query does not give me the result for 2nd or 3rd subsequent attempts. But when I open a separate session from the PL/SQL developer, login as appowner and execute the query then the query gives me the correct result for the end user.

Can some one explain the reason for this strange behavior?. Is there any problem with the database cache?.

Thank you in advance.
Re: Query not execute for the first time [message #353227 is a reply to message #353222] Mon, 13 October 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We need your version with 4 decimals.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Query not execute for the first time [message #353360 is a reply to message #353227] Mon, 13 October 2008 09:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
First line of the post:
Hi can someone help to identify the strange behavior in my Oracle 9.2.0.2.0 database.


When you say 'When I execute this from the end user....' what do you mean?
do yuo mean:
1) when the application, connected to the database as the end user executes this query
2) when the application, connected to the database as you executes this query
3) when execute this query through a development tool like pl/sql developer, while connected as an end user
4) Other
Re: Query not execute for the first time [message #353499 is a reply to message #353222] Tue, 14 October 2008 01:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
sounds to me like you got a bug in your code.

Quote:
same query
same user
same data
same query tool

equals

Quote:
same answer

I suspect there are many details you are not telling us about. For example, what tools are you using to execute this query. Some java app maybe, that does its own connecting, or maybe some stateless web app? In these situations I can think of several bugs that could cause this:

Quote:
1) virtual private database (or similar homegrown functionality) is applying different filters each time you query
2) your stateless app is sometimes connecting to the wrong database and you don't know it
3) your query is actually referencing plsql package variables and these are out of synch (is that ACCOUNTING_BALANCE_AUTH a view that uses packages perhaps?)
4) you are connecting through a stateless web app via a third party connection pool manager like weblogic and you are seeing data artifacts and session state from the previous user session that had the pooled connection you have just been given (this is one of my favorites)


Answer some of our questions so we can help more.

Kevin

[Updated on: Tue, 14 October 2008 01:03]

Report message to a moderator

Re: Query not execute for the first time [message #353540 is a reply to message #353499] Tue, 14 October 2008 02:36 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
You have mentioned that it gives a query nowhere in the database, so it does not return a value or it returns a different value when you run it for the first time?
Previous Topic: about bfile (merged 4)
Next Topic: Transaction Management Oracle
Goto Forum:
  


Current Time: Sun Dec 04 08:22:49 CST 2016

Total time taken to generate the page: 0.11445 seconds