Dontcheff

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
  1. Hi Julian,

    1) PL/SQL block is just executed, there is no consistency during the evaluation of “sysdate = sysdate” loop condition check. Evaluation is done from left to right here whenever that piece of code will run, each sysdate at a time. This loop is for surely finite depending of the cpu speed, the moment it starts in respect with the next second and some random factor during the.switch to next second. If we replace sysdate with systimestamp, we will found that the code will exit the loop much faster as the granularity of time measurement is increased.

    2) SELECT statement is consistent with the it’s beginning, so implicitly should be the time functions SYSDATE and SYSTIMESTAMP, also. The second loop is infinite.

    3) MATERIALZE hint will force the creation of temporary table so the column 0/0 has to be evaluated, hence raising the error. INLINE hint …probably is optimized and because the value of 0/0 is not used, it will not be evaluated as “select /*+ inline */ 0/0 from DUAL” is equivalent to “select null from dual” from this exact WITH query perspective.

    Best regards,
    Eugen

  2. Hi Julian,

    Having this quiz with sysdate in mind, I can think of one tricky hybrid sysdate approach (sql & plsql) that we could see in the field: populating a date or timestamp column through a trigger, “just in case” it is missed.

    –drop table test;
    create table test(date1 timestamp default systimestamp, date2 timestamp, date3 timestamp);

    create or replace trigger test_bi before insert on test for each row
    declare
    begin
    if :new.date3 is null then
    :new.date3:=systimestamp;
    end if;
    end;
    /

    insert into test(date2) select systimestamp from dual connect by level<=power(10,6);
    commit;
    — 1,000,000 rows inserted.

    select count(distinct date1) date1_distinct, count(distinct date2) date2_distinct, count(distinct date3) date3_distinct from test;
    /*
    DATE1_DISTINCT DATE2_DISTINCT DATE3_DISTINCT
    ————– ————– ————–
    1 1 428
    */

    select distinct to_char(date1, 'YYYY-MM-DD HH24:MI:SS.FF9') date1_unique, to_char(date2, 'YYYY-MM-DD HH24:MI:SS.FF9') date2_unique from test;
    /*
    DATE1_UNIQUE DATE2_UNIQUE
    —————————– —————————–
    2018-10-29 14:31:31.278000000 2018-10-29 14:31:31.278000000
    */

    select to_char(min(date1), 'YYYY-MM-DD HH24:MI:SS.FF9') min_date3, to_char(max(date3), 'YYYY-MM-DD HH24:MI:SS.FF9') max_date3 from test;
    /*
    MIN_DATE3 MAX_DATE3
    —————————– —————————–
    2018-10-29 14:31:31.278000000 2018-10-29 14:31:37.966000000
    */

    Best regards,
    Eugen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: