Take a closer look at the following two PL/SQL blocks. Only by looking at them, can you tell what will be the outcome of each one? Let me give you a hint: don't they look as an ideal cases for infinite loops? So, which one of them will run infinitelly (or untill you kill the session)? Both of them? None of them? Remember, only by looking at the code, not by running them!
If you can't stand the temptation and will actually run them, can you give the explanation of the outcome?
WHILE sysdate = sysdate LOOP
SELECT null INTO x FROM dual WHERE sysdate = sysdate;
WHEN NO_DATA_FOUND THEN EXIT;
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Ok, I'll bite. Remember, this is just by looking at the code, not by running it. ;)
My guess is that the key depends upon how Oracle actually executes sysdate=sysdate. Is it smart enough to know that the same function is called twice and thereby only executes it once? Or is it two separate invocations?
If it is a single execution, then they both would run indefinitely since, by definition a will always equal a.
If it is two executions, then they both will terminate at some point because the second sysdate invocation will eventually occur at a different second than the first.
I think the second case is an infinite loop. I think oracle always evaluates the right hand side of the where clause first and this sysdate will always be a few milli seconds earlier than the sysdate on the left side.
But in the first case both the sysdates have the same value substituted before the loop starts.