11 June 2010

Some advice on answering a PL/SQL Challenge quiz

I received this email from a PL/SQL Challenge player recently:

"In the quiz for 11/06/2010, there was a question to identify the schame-level program or packaged subprogram provided by Oracle that can be used to identify if a file exists or not.

"In that, there was one of the options was FILE_EXISTS. As we've packaged subprogram webutil_file.file_exists, I was not sure whether to choose this option or not because of below reasons:

"1) if I don't choose file exists then it may be that you expect us to choose file_exists also (as there is a packaged subprogram named file_exists)

"2) If I choose file exists then it may be that you were expecting that we choose only webutil_file.file_exists should be choosen and not file_exists alone

"So in this way I was not sure whether file_exists alone was a correct choice to choose or not. Please help me understanding your views for this confusion."

In the case of this quiz, three of the four choices offered were of the form "UTL_FILE." and one was simply "FILE_EXISTS". It seems to us that the only reasonable conclusion to draw is that this reference to "FILE_EXISTS" without a package name could only mean a schema-level program, and not any subprogram with that name in any supplied package from Oracle. 

More generally, we recommend that you interpret the quiz question and choices as literally as possible. Do not make assumptions (beyond those we show you before you take the quiz). That puts the burden on us to provide quizzes without any ambiguity - which turns out to be harder than we thought it would be. But we believe we are getting better at it.

10 June 2010

Response to questions about 9 June 2010 quiz: Autonomous transactions(363)

A number of players got in touch with us regarding this quiz question for 9 June 2010: "Which of the following choices state valid rules for defining a program as an autonomous transaction?" We said that these choices are correct: 1. If within that program a change is made to at least one table, you must perform a commit or rollback before terminating the program. Otherwise, an exception will be raised by Oracle and a rollback performed in your session. 2. The AUTONOMOUS_TRANSACTION pragma must appear in the declaration section of the program. And we claimed these are incorrect: 3. The AUTONOMOUS_TRANSACTION pragma must appear in the first line of the declaration section. 4. If any change is made to a table in the program, you must commit that change before returning control to the main transaction. 5. You can execute only static SQL statements inside an autonomous transaction. We respond below to the feedback we received (referencing choices by the numbers shown above), but the bottom line is this: We agree that choice (1) is in fact incorrect. Anyone who marked this as wrong will receive credit and ranking will be adjusted.

Choice 1 is Actually Incorrect

According to the Oracle 10.2 documentation http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1537 section Avoiding Errors with Autonomous Transactions, we find this: "If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, the transaction is rolled back." Consequently, the last sentence of the choice should be (correction in italics): "Otherwise, an exception will be raised by Oracle and a rollback performed on any changes made in the autonomous transaction program unit" not "a rollback performed in your session."

Choice 4: Change made to a Table

A player wrote that "change" "to a table" doesn't mean only table data, raising questions regarding the impact of DDL changes to the table as in:
declare
 /*create table a (d date, v varchar2 (8)); */
 pragma autonomous_transaction;
begin
 insert into a values (sysdate,'o');
 execute immediate 'truncate table a';
 execute immediate 'alter table a add (b varchar2(8))';
 execute immediate 'alter table a drop column b';
end;
You wouldn't have to do a commit explicilty in this case, because Oracle will do so implicitly. Regardless, this choice is incorrect, because you do not have to commit at all with an autonomous transaction. You could decide to rollback instead. So there is a possible ambiguity in the choice, but the ambiguity does not affect the correctness of the answer.

Choice 4: Rolling Back Table changes

A player wrote: "This option of the quiz doesn't indicate that a rollback is not possible." Well, the point of this choice is to make sure you understand that a commit is not required; either commit or rollback is required to terminate the autonomous transaction program unit without an exception being raised by Oracle.

Error and Exception?

A player wrote: "Regarding the first answer of the quiz for Wednesday 9 June 2010: shouldn't a distinction be made between an error and an exception? If a commit or rollback is omitted in the autonomous transaction an error occurs. This could result in an exception being raised, but first you would have to declare that exception and associate it with this particular error both of which has not necessarily been done yet." We do not think there is any problem with this. An exception is raised by Oracle. You don't need to declare any exception. You may be confusing Oracle error numbers with exceptions. In PL/SQL, an error condition is called an exception.

08 June 2010

Response to questions raised about 7 June quiz: %ROWTYPE(361)

A number of players got in touch with us regarding this quiz question:

"Which of the following statements accurately describes the effect of using the %ROWTYPE attribute to declare a record variable or parameter?"

We address the concerns people have raised below, but first the bottom line: we believe that there was no mistake or ambiguity in this quiz and we will not be rescoring.

1. One choice, which is correct, stated "The record defined with this attribute will have a field for each column in the table specified."

Two people raised a question about that choice. One person noted that you can use %ROWTYPE to declare a record based on a cursor, in which case you would have a field for each expression in the select list of the cursor. That is true, but the above statement still holds, since it clearly implies ("table specified") that a declaration of the form %ROWTYPE was used to declare the variable/parameter.

The second person asked: "Does %ROWTYPE attribute include virtual columns defined for the table?" And the answer is yes, as you can tell by running the following two statements:

CREATE TABLE sales_with_vc(
    sales_id    NUMBER
  , cust_id     NUMBER
  , sales_amt   NUMBER
  , sale_category VARCHAR2 (6)
  GENERATED ALWAYS AS (
     CASE
        WHEN sales_amt <= 10000 THEN 'LOW'
        WHEN sales_amt > 10000 AND sales_amt <= 100000 THEN 'MEDIUM'
        WHEN sales_amt > 100000 AND sales_amt <= 1000000 THEN 'HIGH'
        ELSE 'ULTRA'
    END
  ) VIRTUAL
)
/

DECLARE
  l_rec   sales_with_vc%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line (l_rec.sale_category);
END;/

2. A half dozen developers objected to us considering this statement to be wrong: "Use %ROWTYPE to define a PL/SQL collection of multiple rows of the specified type."

As one player put it, "Why is the last answer (use %rowtype for a collection) wrong? I tested this chunk of code and it works perfectly."

DECLARE
  TYPE coll_rowtype IS TABLE OF landen%ROWTYPE INDEX BY PLS_INTEGER;
  collection_landen  coll_rowtype;
  CURSOR c_lnd IS SELECT * FROM landen;
BEGIN
  FOR r_lnd IN c_lnd
  LOOP
     collection_landen (r_lnd.id) := r_lnd;
  END LOOP;
END;
/

And this player is right, you can use %ROWTYPE in a TYPE statement to "define a PL/SQL collection of multiple rows of the specified type."

The reason this choice is not a correct answer is that the question asked about "using the %ROWTYPE attribute to declare a record variable or parameter." Not a collection variable or parameter.

We look forward to your thoughts on this topic.

Learning from the PL/SQL Challenge

From the desktop of Steven Feuerstein

Working on the PL/SQL Challenge has been a humbling experience. If a person is familiar with my professional career, they probably would agree that I am good at writing and know PL/SQL well. So you'd think it would be a "no brainer" to write hundreds and hundreds of clear, unambiguous and interesting quizzes.

Hah!

In fact, it has been a very, ahem, challenging experience - and also a learning experience. Not only have I been introduced to nuances of the Oracle technology stack with which I was not previously familiar, but I have also been reminded of how difficult it can be to construct sentences that have no ambiguity in them.

Here's the way one person put it: "You need to get better at writing the questions. Often they are ambiguous, I find and I've been writing PL/SQL for about 15 years. I'm not saying I know everything by a long shot, just that I find some of the questions and possible answers to be confusing."

Now, I suppose that we (Finn and I, the founders of the PL/SQL Challenge) could take the approach of "standing by" our quizzes and refusing to acknowledge the questions and criticism that come in, sometimes at a rapid clip. We could avoid talking about mistakes we have made.

We have decided, instead, to be as open as possible. If we mistake, we fix it (adjust scores, re-rank). We tell everyone what we are doing. We give a prize to the first person who identifies a mistake in a quiz. Along the way, we clarify our assumptions but also work on tightening up our language. Hopefully we also retain the trust of our players, many of whom have told us that they have greatly enjoyed the PL/SQL Challenge and learned much from it already, partly because it generates discussion on their team about PL/SQL capabilities.

So please do keep playing the quiz and continue to notify us of any problems you encounter with a quiz or with the website.

Warm regards, Steven

06 June 2010

Release 1.3 of PL/SQL Challenge Now Available!

We have now published a new version of the PL/SQL Challenge website. We have redesigned the web pages to provide you with more ranking and profile information. We hope you like it. Press the Feedback button if you have any comments or suggestions. Here is a list of key changed or improved features:
  • New home page displays more ranking information and also shows you upcoming prizes you can win.
  • New login and profile information in upper-right corner of web pages.
  • New Statistics page offers much more and more detailed information. Note: we will also soon publish instructions so that you can write your own reports to display on this page!
  • New Testimonials page offers quotes from players of the PL/SQL Challenge.
  • New Welcome page for first time players.
  • The monthly Toad World (www.toadworld.com) quiz is now hosted at PL/SQL Challenge.
  • Lots of cleanup of UI inconsistencies.
We hope you like the new website design. Most changes were motivated by specific requests from players.