Home » SQL & PL/SQL » SQL & PL/SQL » Different result when using FOR UPDATE (Oracle 11)
Different result when using FOR UPDATE [message #663498] Tue, 06 June 2017 08:31 Go to next message
hundsmiachn
Messages: 2
Registered: June 2017
Junior Member
Hi

I have a strange problem, maybe someone can help me:

Following Statement:

SELECT ( SELECT TABLE1.JANEIN FROM TABLE1 T1 WHERE TOURP.FK1 = T1.PK ) AS RESULT
FROM TOURP
WHERE TOURP.TOURNR = 'FM0000000FM'
FOR UPDATE OF TOURP.TOURPNR NOWAIT;

The RESULT has a constraint and can either be 'J' or 'N'. The strange thing now is that I get a 'J' if I dont use the "FOR UPDATE NOWAIT" and a 'N' when I use it. ('N' is the default value)

Could anybody explain to me why this can happen, should the result not be the same, if I lock the data or not ??

thanks in advance
regards
Erich
Re: Different result when using FOR UPDATE [message #663501 is a reply to message #663498] Tue, 06 June 2017 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Is it nice that you actually found, read, & posted formatted SQL.
http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz

how can we reproduce what you report?
Re: Different result when using FOR UPDATE [message #663502 is a reply to message #663501] Tue, 06 June 2017 09:14 Go to previous messageGo to next message
hundsmiachn
Messages: 2
Registered: June 2017
Junior Member
Hi

Sorry for the formatting. It is more an example on how I use the statement.
My question is more a general question if it is possible, that the outcame of a query depends on the "FOR UPDATE" or not.
My understanding is that the result has to be the same (the data dont change of course).
Is it possible that this is a integrity problem of the database ?

thanks
regards
Erich


Re: Different result when using FOR UPDATE [message #663503 is a reply to message #663502] Tue, 06 June 2017 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
hundsmiachn wrote on Tue, 06 June 2017 07:14
Hi

Sorry for the formatting. It is more an example on how I use the statement.
My question is more a general question if it is possible, that the outcame of a query depends on the "FOR UPDATE" or not.
My understanding is that the result has to be the same (the data dont change of course).
Is it possible that this is a integrity problem of the database ?

thanks
regards
Erich


It may be bug.
It may be that what you report does not match reality.
I suspect that you are doing something weird & Oracle is behaving correctly.

If it is a bug, then you'll need to provide Oracle MOS a reproducible test case.

How can we reproduce what you report?

Why do you have "nested" SELECT statements?
Re: Different result when using FOR UPDATE [message #663505 is a reply to message #663498] Tue, 06 June 2017 09:56 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
hundsmiachn wrote on Tue, 06 June 2017 15:31
...
SELECT ( SELECT TABLE1.JANEIN FROM TABLE1 T1 WHERE TOURP.FK1 = T1.PK ) AS RESULT
FROM TOURP
WHERE TOURP.TOURNR = 'FM0000000FM'
FOR UPDATE OF TOURP.TOURPNR NOWAIT;
...
NEVER use SELECT in SELECT clause unless you are an expert in SQL.
First of all, rewrite your query then we can see if you have an actual problem.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Previous Topic: dbms_output.put_line for dynamic sql query
Next Topic: Handling invalid identifier exception in plsql
Goto Forum:
  


Current Time: Thu Mar 28 03:54:24 CDT 2024