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?

Two Mistakes in 12 May Quiz (2304)

Well, let's just start off with an apology: I was sloppy and as a result must alter the scores of 548 players for this day. Maybe it's because I was so excited at the end of last week when I found out that I will be a grandfather come November. No, that's a lousy excuse (though great news).

In any case, players wasted no time in reporting two problems with this quiz on the COUNT method:

1. We scored as incorrect a choice that resulted in an unhandled exception being raise. This error clearly indicates that the implement in that choice was flawed. The question, however, asked which choices resulted in "3" being displayed. When this choice is executed, "3" is displayed and then the error is raised. In most editing environments, you will see this output even when the block terminates with an exception.

Action taken: I changed the choice to include an exception handler that will display "ERROR" and changed the question text to ask for choices that only show "3". Anyone who marked that choice as correct has been given credit.

2. We did not include an ORDER BY on the query in the question, so the order of data placed in the collection cannot be guaranteed. THIS OMISSION REALLY IRRITATES ME - and should irritate you, too. Again, my apologies. This issue has been raised multiple times by players (for past quizzes) and I really need to be more careful.

Action taken: I added an ORDER BY to the query. Everyone who marked as incorrect the two intended-to-be-correct choices are given credit.

I also refreshed the rankings, so all data on the website should reflect the above steps.

A "good to know" regarding explain plans and DBMS_XPLAN (2303)

The 11 May quiz tested your knowledge of the DBMS_XPLAN package and how it can be used to display explain plan information. As part of the explanation of the scoring, we wrote:

This choice does not specify a statement ID, but that's OK,  because the call to the DBMS_XPLAN.display function does not pass a value for  the statement ID parameter. Oracle automatically passes back explain plan  information for the most recently explained statement.

Anna Onishchuk wrote to suggest that this statement is not entirely true:

Oracle does not pass back explain plan information for the  most recently explained statement, it passes back information for the highest  PLAN_ID which is quite different in databases with DB links. So in most cases  your explanation is correct, but not in all cases. So technically the answer should be “As we have a totally  clean database (no db links), you’ll see the plan for the last query you  run”.

She also offered this example:

If you run a query for a remote table via DB Link, the Oracle will take sequence from the remote database and use it in the local database. Here is an example:

You current sequence plan_id = 20 in your local database.
In remote database plan_id= 1000.

Run the query:

Explain plan for 'select * from dual@my_link where 1=1'
/

The query will place new records in PLAN_TABLE with PLAN_ID = 1001. If you try to do the quiz, the greatest PLAN_ID will be selected so the remote plan will be returned. (Unless you try the quiz for 800 times)

SELECT *
  FROM TABLE (DBMS_XPLAN.display ('PLAN_TABLE', NULL))
/

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |  1 |  1 | 3 (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL     | DUAL |  1 |  1 | 3 (0)| 00:00:01 |

===== End of example

I will update the explanation for the question with this information, but I thought it would also be worth highlighting it on the blog.

Thanks, Anne!

Final test period for PL/SQL Challenge version 2

Dear PL/SQL Challenge player,

I am pleased to inform you that test.plsqlchallenge.com is now open for testing of version 2 of the PL/SQL Challenge website.

The test period will last through 20 May. We then plan to upgrade PLSQLChallenge.com over the weekend of 21 May.

You should be able to log in using the same information you use at plsqlchallenge.com. All of your quiz results through the end of last week are loaded up.

Remember: any information you enter on the test site (quizzes taken, changes to profile, etc.) will not be carried over to the production site.

As you will see, version 2 of the website is very different from the current state of the site. We hope that you like changes! Besides a new new look and feel, the biggest change you will notice immediately is that the site is no longer focused around a single, daily quiz. There is no longer, for example, a button in the menu bar for Take Quiz. Now you simply pick the quiz you want to take from the Pick a Quiz table.

This is just one reflection of the fact that the site is now powered by a much more flexible platform for delivering quizzes. We will, as a result, soon be adding SQL and PL/SQL quizzes. We also now offer polls, right on the site, so that we can more easily gather feedback from you on how to improve the PL/SQL Challenge.

When you notice a problem with the site and click on Feedback to report a bug, please make sure to view the report on the bottom of the page (especially the Known Issues report) to avoid submitting an issue of which we are already aware.

You can also respond to this blog post with your feedback.

Many thanks in advance,
Steven Feuerstein