I quizzed an Oracle community

Once was a time when I read a few questions every morning at AskTom. It only took a few minutes but the amount of hints, tips, tricks and general best practice guidelines I learned became so valuable as my experience grew.

I still find it a valuable resource, but these days as a seasoned developer, I found myself a solution with a different style of learning - keeps my mind active and I learn the odd thing occasionally, other times it helps affirm my current understanding of Oracle behaviour.

Steven Feuerstein and his friends have created a (business) daily PL/SQL Challenge. You may have heard about it and wondered if you should give it a go - for most probably a variety of reasons.
Well, you don't even have to compete for the frequent prizes - you can have a private profile; or you may see people providing a link to their public profiles on their blog; or as a resume item.

The main thing is the occasional "test your knowledge" - typically a little snippet of code to have you thinking for a couple of minutes.

Steven has provided players an opportunity to submit their own quiz questions, so if you're curious of the sort of questions that get asked, here's mine from 28th February 2011 - it's a lesson on Short Circuit Evaluation:

---

I create and populate a table as follows:

CREATE TABLE plch_parts
(
   partnum    INTEGER
 , partname   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_parts
        VALUES (1, 'Mouse');

   INSERT INTO plch_parts
        VALUES (100, 'Keyboard');
   COMMIT;
END;
/

Then I define the following function:

CREATE OR REPLACE FUNCTION part_exists (pn_partnum  IN  plch_parts.partnum%TYPE)
RETURN BOOLEAN IS
  ln_partname  plch_parts.partname%TYPE;
BEGIN
  SELECT partname
  INTO   ln_partname
  FROM   plch_parts
  WHERE  partnum = pn_partnum;

  DBMS_OUTPUT.PUT_LINE(ln_partname);

  RETURN TRUE;
EXCEPTION WHEN NO_DATA_FOUND THEN
  RETURN FALSE;
END part_exists;
/

Which of the choices, when used in place of the /*IF CONDITION*/ comment in the following block,
will result the subsequent output being displayed on the screen after the block is executed?

BEGIN
  /*IF CONDITION*/
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

Mouse
Keyboard
TRUE

---

A	IF part_exists(1) OR part_exists(100) THEN
B	IF NVL(part_exists(1) ,part_exists(100)) THEN
C	IF COALESCE(part_exists(1) ,part_exists(100)) THEN
D	IF part_exists(1) AND part_exists(100) THEN
E	IF CASE WHEN part_exists(1) OR part_exists(100) THEN TRUE END THEN

---

The rules are well written, the idea is well presented (unlike my hand written html table representation here), and the user feedback is fantastic. Steven also has an associated blog for constructive discussions on the occasional contentious question, or question of general interest or quirkiness.

My question was well received by some friends, and Steven now provides users with simple, key survey results from each question - and I was happy with mine.

The answer is B & D - "NVL" & "AND"
I provided the following verification code to assist explanation of the answer:

BEGIN
  -- As soon as the first expression returns true, PL/SQL no longer needs to evaluate the second. This is perfect for situations where you can put more efficient calculations first.
  IF part_exists(1)
  OR part_exists(100) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- Ideally this would behave as per COALESCE, but unfortunately NVL does not support short circuit evaluation.
  IF NVL(part_exists(1)
        ,part_exists(100)) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- COALESCE is ANSI-defined shorthand for similarly written CASE functions, thereby supporting short circuit evaluation. COALESCE is generally preferred to NVL.
  IF COALESCE(part_exists(1)
             ,part_exists(100)) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- Since this uses the AND operator, both expressions need to be evaluated to determine if the entire boolean equation is true. Oracle would use short-circuit evaluation if the situation permits, for instance, if the first expression returned false the second would not be evaluated.
  IF part_exists(1)
  AND part_exists(100) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- The Oracle database will use short circuit evaluation for CASE expressions.
  IF CASE WHEN part_exists(1)
  OR part_exists(100) THEN TRUE END THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

There are currently over 1000 regular players, but only about two dozen regulars are registered as Australian - I've seen more than that at our AUSOUG branch meetings! Come on Aussies - "represent!"

It's a great learning & affirming tool - I'll be on the lookout for inspiration for submitting more questions. :-)

ScottWe

ps - no, you can't cut & paste the code while a question is active