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.