IBM signs on as PL/SQL Challenge Premier Sponsor

As you may have noticed last week, IBM is now a premier sponsor of the PL/SQL Challenge!

You might think it odd, at first glance, that IBM, with its own competing relational database technology (DB2) , would sponsor an Oracle PL/SQL-focused website.

In fact, it makes perfect sense, because as you can read right here, DB2 9.7 can run Oracle applications on Linux, UNIX, and Windows! 

To quote the link above:

"IBM DB2 9.7 for Linux, UNIX, and Windows has out-of-the-box support for Oracle's SQL and PL/SQL dialects. This allows many applications written against Oracle to execute against DB2 virtually unchanged. In this article, get a high-level overview of what Oracle compatibility means in DB2. Whether you want to switch your custom application to DB2 or extend your DBMS vendor support to DB2, now is your time."

That's right - IBM has implemented support for compilation and execution of PL/SQL program units in the DB2 9.7 database!

IBM's motivation for doing this is clear: they want to make it easier (and far less expensive) to convert from Oracle to DB2. You might then ask: "Why would the PL/SQL Challenge help IBM move customers off of Oracle?" That answer should be clear enough: this website is all about helping PL/SQL programmers make the most of this database programming language. It shouldn't matter if you are building or maintaining applications in an Oracle database or an IBM database. PL/SQL is PL/SQL.

And while from the standpoint of Oracle Corporation, it may not be the greatest thing in the world that PL/SQL programs will become increasingly portable outside of Oracle, that could be a very good thing for the careers of PL/SQL programmers.

IBM does not yet support every single nuance of PL/SQL syntax, built-in packages and underlying architectural elements, but they are working hard on extending their compatibility.

We encourage you to check out IBM's detailed explanation of what they support and how they've gone about implementing their PL/SQL compatibility.

Finally, many thanks to IBM for joining our list of premier sponsors!

Steven Feuerstein
Founder, PL/SQL Challenge

New feature: Open Question in New Window

With the questions and choices on a single long page, as you scroll down to evaluate your choices, you can lose sight of the question. For questions with lots of choices or choices with lots of code, players found themselves "bouncing" up and down on the page: read the choice, go back to the question, back down to the choice.

Not very user-friendly.

To address this problem we now offer a hyperlink to "Open Question in New Window" directly under the text of the question. Click on this link to open a new browser that contains both the question and choices. You can then scroll down through the choices on the "take the quiz" page, and still see the question text. You can, of course, also use this extra window to compare two different choices, more easily identifying the differences.

We hope this improves your quiz-taking experience.

Cheers, Steven Feuerstein

SQL and APEX Quizzes: Authors and Reviewers Needed!

Now that PL/SQL Challenge Version 2 is in place, it is time to fulfill my promise of starting up weekly SQL and APEX quizzes! And to do this, we could use your help.

To hold weekly quizzes, we need quizzes and we need people to review those quizzes.

Apex Evangelists, led by APEX experts Dimitri Gielis and John Scott, will be taking the lead on the APEX quizzes, making sure that a new quiz is ready every week, responding to questions, and so forth. I am sure that they will come up with many fine quizzes, but (as with PL/SQL) each of you have your own experiences and expertise that you can share through quizzes that you write. In addition, Dimitri and John need some independent eyes to look over their (and others') quizzes, to ensure they are accurate and error-free.

I will manage the SQL quizzes, but I am no SQL expert and will be hard-pressed both to come up with top-notch quizzes and also make sure they are free of errors and ambiguities.

So here's how you can help:

Sign up to be a reviewer.

First, note that if you become a reviewer you can still take any quiz that you have not viewed as a part of the review process. It is no longer an "all or nothing" proposition, as was the case in PL/SQL Challenge 1.X.

First, update your profile so that it clearly describes your experience and accomplishments in the SQL and/or APEX technologies. If we cannot see from your profile that you are experienced with these technologies, we will not approve your proposal to become a reviewer.

Next, open the Feedback page, select "Volunteer as Reviewer" for the Feedback type. Then in the comments section provide a summary of why you want to become a reviewer and anything else that will help us make a decision on approving you.

We will then follow up with you regarding your offer to volunteer.

Write a quiz!

Write and submit proposals for quizzes. Simply press the "Submit Quiz" button on the menu, read the instructions, and then fill in all required fields.

There are many benefits to authoring a quiz, including high visibility when your quiz is played and receiving maximum possible score for that day.

Thanks in advance for your support and contributions. We will announce start dates for both of these quizzes soon.

Steven Feuerstein

Should We Test Knowledge of "Bugs" in Quiz? (2344)

The 24 May demonstrates the impact of using a constrained subtype for the datatype of an associated array index, as well the fact that when using a BULK COLLECT fetch to populate that collection, the constraints of the type are ignored.

Two players complained that they don't think it makes sense for a quiz to test one's knowledge of a bug. I offer their comments below and will leave it to the author first to reply with her own "story behind the quiz", and of course publish any other comments as well.

"While this is obviously known behaviour, I feel a bit ripped off in getting this wrong. I answered what was logical (to me) from the code - the array definition says the index must be -1/0/1, bulk collect will use index values 1/2/3, so bulk collect will error. To me, the behaviour of bulk collect ignoring the index by's data type constraints seems like a bug. If this is documented by Oracle as being expected behaviour, it would be good to have that reference in the answer. If not, it seems a bit rough to mark people incorrect for not knowing about a bug."

and with some code, too:

When you run the code of this quiz, Oracle does actually make from an INDEX BY SIGNTYPE table an 'read-only' INDEX BY PLS_INTEGR table. You can only change the values for index (-1, 0, 1). See the following code:

DECLARE
   TYPE t_bug_type IS TABLE OF all_source%ROWTYPE
                         INDEX BY SIGNTYPE;

   v_bugs   t_bug_type;

   v_ndx    PLS_INTEGER;
BEGIN
   BEGIN
        SELECT *
          BULK COLLECT INTO v_bugs
          FROM all_source
      ORDER BY owner, name, line;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('ERROR');
   END;

   DBMS_OUTPUT.put_line (
         'First = '
      || v_bugs.FIRST
      || ' Last = '
      || v_bugs.LAST
      || ' Count = '
      || v_bugs.COUNT);

   v_ndx := v_bugs.FIRST;

   DBMS_OUTPUT.put_line (v_bugs (v_ndx).text);

   v_ndx := v_bugs.LAST;

   DBMS_OUTPUT.put_line (v_bugs (v_ndx).text);

   BEGIN
      v_bugs (v_bugs.LAST).text := 'Oracle has many bugs !!';
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'You should not create quizzes based on bugs !!');
   END;
END;
/

Oracle has many bugs, in my opinion it is not intended to make quizzes based on bugs.

So...what do you think?

PL/SQL Challenge Version 2 Now Available!

Version 2 of the PL/SQL Challenge is a major upgrade of this popular site for Oracle technologists. We've been developing version 2 for over six months (where did all that time go?) and we are extremely pleased with the results. I hope you will feel the same way.

There are many changes to the site, highlights of which I offer below:

• New and improved interface: earlier versions of the website were very text-heavy, with minimal use of color and images. We believe that the new site will be easier and more enjoyable to use.
• More flexible architecture and offering of quizzes: PL/SQL Challenge V1.X was built completely around the daily PL/SQL quiz. This approach wasn't really flexible enough to handle existing requirements (such as the monthly Toadworld quiz and the quarterly playoff), and it certainly wouldn't let us easily add other kinds of quizzes on other technology areas. V2 puts in place a platform that not only offers more flexibility and clarity today, but will make it easy to add more quizzes in the (near) future.
• Guest-related features: now, even before you register, you can check out up to five quizzes in the library and play a sample quiz. We hope this will make the site more welcoming and encourage more technologists to play. If you visit the site and you are not yet logged in, you will now see a Welcome page that offers these features to guests
• New messages system: no more ticker across the page, we now offer a separate tab on the menu, Messages, in which you can view both news, broadcast to all players, and personal messages, directed specifically to you. When you have unread messages, the Message button will blink.
• On-line reviewing: now both reviewers and quiz authors can review, comment on, and edit questions. This will make it easier for the broader player community to help build and quality-check the quizzes.
• Polls: you can, right within the website, take polls to help us enhance the PL/SQL Challenge experience for all players. Polls are built on the same foundation as quizzes, so it was a nice verification of the generalized platform we built. We hope that you take the time to take those polls and make a difference at the PL/SQL Challenge!
• Behind the scenes: as much as the player-facing pages of the PL/SQL Challenge website has changed, the "backend" component of the application has also been transformed. We are now automating many more of the tasks required to operate the PL/SQL Challenge. This will make us more efficient and responsive.

The website is very different, offering more features, but hopefully in an intuitive fashion. We plan to  record videos exploring these new features; we'll let you know when they are available. Our deepest gratitude to the many Challenge players who participated in the beta and final test phases of our development. Your feedback, critiques and suggestions have resulted in major improvements to the site. And, of course, we welcome all of your comments now that the site is live. I will, by the way, also shortly announce the date for the Q1 2011 playoff, which I have put off until this new architecture is in place. We did a "test" playoff last week, and the feedback from players on the improved usability was very positive. So...we hope that you enjoy the new website, and wish you the best as you play the PL/SQL Challenge! For the PL/SQL Challenge development team, Steven Feuerstein

Invoker Rights and Function Result Cache - Oh, Sloppy Compiler! (2322)

In the 17 May quiz, we asked:

Which of the following statements describe what happens when you combine invoker rights (AUTHID CURRENT_USER) with the function result cache?

and the only choice we scored as correct was:

If you include both AUTHID CURRENT_USER and RESULT_CACHE in the header of your function, Oracle will raise a compilation error.

Specifically, the following choice was scored as incorrect:

You can include both AUTHID CURRENT_USER and RESULT_CACHE in the header of your function, but the CURRENT_USER setting will be ignored.

It didn't seem like this would be a very problematic quiz, since Oracle does in fact have an error defined for just this scenario:

PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules 

And we verified this with a package as follows:

CREATE OR REPLACE PACKAGE plch_pkg
   AUTHID CURRENT_USER 
IS
   FUNCTION object_name (object_type_in IN all_objects.object_type%TYPE)
      RETURN all_objects.object_name%TYPE
      RESULT_CACHE;
END;
/

That's pretty clear, right? Ha!

Turns out that while all of the above is unambiguously correct for packages, when it comes to functions, the PL/SQL compiler is downright funky - and it messed up our quiz! Check this out (many thanks to Iudith for the code example and detailed analysis):

SQL> CREATE OR REPLACE FUNCTION F2 (p_id in number)
  2    RETURN VARCHAR2
  3    RESULT_CACHE
  4    AUTHID CURRENT_USER
  5  AS
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('*** INSIDE FUNCTION ***');
  8    RETURN 'ABC';
  9  END;
 10  /

Function created.

SQL>
SQL> CREATE OR REPLACE FUNCTION F1 (p_id in number)
  2    RETURN VARCHAR2
  3    AUTHID CURRENT_USER
  4    RESULT_CACHE
  5  AS
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('*** INSIDE FUNCTION ***');
  8    RETURN 'ABC';
  9  END;
 10  /

Warning: Function created with compilation errors.

SQL>
SQL> sho err
Errors for FUNCTION F1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/10     PLS-00999: implementation restriction (may be temporary)
         RESULT_CACHE is disallowed on subprograms in Invoker-Rights
         modules

If when you define your function you include the RESULT_CACHE keyword after the AUTHID CURRENT_USER clause, you will see the above error. If, however, you reverse the order of those clauses, the function compiles - but the results are not cached.

Now that right there is funky stuff.

We will take the following steps for this quiz:

1. Everyone gets credit for the two choices listed above (bad luck that this affected two of the choices).Your answers will be changed to reflect this.

2. We will change the text of the question so that it explicitly asks about using both invoker rights and result cache in a package. That way this ambiguity will be avoided.

3. We'll notify the PL/SQL product manager about this glitchy behavior, just in case they are not aware.

I look forward to your comments.

Steven

Using double quotes in trigger event functions (1424)

In the 16 May quiz, we tested your knowledge of ways to restrict firing of a trigger based on the column being updated. The following choice was scored as correct:

CREATE OR REPLACE TRIGGER plch_employees_trg
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('salary')
   THEN
      sys.DBMS_OUTPUT.put_line ('Updated');
   END IF;
END;
/

And this explanation was provided: "This choice relies on the UPDATING function to determine if an update is taking place on the salary column, by passing the name of the column to the function. Since I do not enclose the name in double quotes, Oracle will automatically upper-case the name of the column and so will correctly detect that in the case of the first update on last_name, no output will be displayed. As a result, "Updated" is displayed just once."

Two players wrote to note that "11gR2 does not interpret double quotes as enclosing character for the updating function, and automatically upper-case column names even if it has been defined in the table with lower case names."

I put together the following script to examine this more closely:

DROP TABLE plch_employees
/

CREATE TABLE plch_employees
(
   employee_id   INTEGER
 , last_name     VARCHAR2 (100)
 , salary        NUMBER NOT NULL
 , "comment"     VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_employees
        VALUES (100
              , 'Jobs'
              , 1000000
              , NULL);

   INSERT INTO plch_employees
        VALUES (200
              , 'Ellison'
              , 1000000
              , NULL);

   COMMIT;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg1
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('comment')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired comment.');
   ELSE
      sys.DBMS_OUTPUT.put_line ('comment will not fire.');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg2
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('COMMENT')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired COMMENT.');
   ELSE
      sys.DBMS_OUTPUT.put_line ('COMMENT will not fire.');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg3
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('"comment"')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired "comment".');
   ELSE
      sys.DBMS_OUTPUT.put_line ('"comment" will not fire.');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER plch_employees_trg4
   AFTER UPDATE OR INSERT
   ON plch_employees
   FOR EACH ROW
BEGIN
   IF UPDATING ('"COMMENT"')
   THEN
      sys.DBMS_OUTPUT.put_line ('Fired "COMMENT".');
   ELSE
      sys.DBMS_OUTPUT.put_line ('"COMMENT" will not fire.');
   END IF;
END;
/

BEGIN
   sys.DBMS_OUTPUT.put_line ('Update salary column');

   UPDATE plch_employees
      SET salary = 2 * salary
    WHERE employee_id = 200;

   sys.DBMS_OUTPUT.put_line ('Update comment column');

   UPDATE plch_employees
      SET "comment" = 'This is comment for emp no 200'
    WHERE employee_id = 200;

   COMMIT;
END;
/

And the output I see is:

Update salary column
"COMMENT" will not fire.
"comment" will not fire.
COMMENT will not fire.
comment will not fire.
Update comment column
"COMMENT" will not fire.
"comment" will not fire.
Fired COMMENT.
Fired comment.

Very curious. Any thoughts on this?