DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: PUZZLE

  1. #1
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Question

    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?

    Code:
    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;
    /
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #2
    Join Date
    Apr 2001
    Posts
    118
    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

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Excelent guesses, however your answer(s) about the possible outcomes are not correct....

    Anyone else?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    May 2000
    Posts
    58
    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.


  5. #5
    Join Date
    May 2000
    Posts
    58
    Sorry. according to my argument, the first case is an infinite loop.

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.


  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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?

  10. #10
    Join Date
    Jun 2001
    Posts
    316
    i think......

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width