PUZZLE

[jmodic]

Ok, here is a proposal:

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?

BEGIN
  WHILE sysdate = sysdate LOOP
    NULL;
  END LOOP;
END;
/

DECLARE
  x DATE;
BEGIN
  LOOP
    BEGIN
      SELECT null INTO x FROM dual WHERE sysdate = sysdate;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN EXIT;
    END;
  END LOOP;
END;
/

[Heath]

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.

Just a guess.

Great puzzle, though. :)

Heath

[jmodic]

Excelent guesses, however your answer(s) about the possible outcomes are not correct....

Anyone else?

[Victoria]

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.

[Victoria]

Sorry. according to my argument, the first case is an infinite loop.

[julian]

Originally posted by Victoria
But in the first case both the sysdates have the same value substituted before the loop starts.

I disagree with that. The loop will be run through several 100s or 1000s times and then exited.

The second one is infinite.

[jmodic]

Originally posted by Victoria
Sorry. according to my argument, the first case is an infinite loop.

I see you realy are not cheating, you trully are digging toward the right answer just by looking at code . Kudos!

However, I must disappoint you: wrong answer.

[jmodic]

Originally posted by julian
[I disagree with that. The loop will be run through several 100s or 1000s times and then exited.

The second one is infinite.
[/B]

Yes, that is correct! But more importantly, can you explain *why* this is so? Why "SYSDATE = SYSDATE" in the first case sometimes evaluates to FALSE, while in the second case this *never* happens?

[julian]

Beats me :-)

My guess is that in 2nd case Oracle evaluates the WHERE clause at once having always the equality right, while in the 1st case WHILE has different behavior.

How about if you explain that?

[helpme]

i think......

the 2nd 1 will just exit after 1 execution...
1st 1 will go on as an infinite loop