Dontcheff

Archive for October, 2018|Monthly archive page

PL/SQL quiz

In Oracle database, PL/SQL on October 27, 2018 at 23:42

Years ago, I saw this quiz on dbasupport. Below, we have 2 PL/SQL blocks, have a look:


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;
/

Question is: what happens after you run them? Are the loops above both finite, both infinite or is it so that one of them is finite and the other one infinite?

If you cannot answer the question just run them in SQL*Plus, etc. Then, try to explain why – the reason for being finite or infinite.

I will update this blog post after month or so with the answer.

And I have just run this against ATP (~18c) but the output is same in previous versions too:

with SALES as (select /*+ materialize */ 0/0 from DUAL)
select count(*) from SALES;

ORA-01476: divisor is equal to zero
01476. 00000 -  "divisor is equal to zero"
*Cause:
*Action:

with SALES as (select /*+ inline */ 0/0 from DUAL)
select count(*) from SALES;

  COUNT(*)
----------
         1

Updated on October 31st:

As you can see from Eugen Iacob’s answer/comments below, the answer is read consistency. The first loop is finite, the second is infinite. Well done Eugen!

In SQL sysdate is evaluated only once – unlike in PL/SQL.

Advertisements