More semantic analysis - and objections - in 28 April quiz (2244)

This quiz asked: "Which of these choices describe a way in which a value can be assigned to a PL/SQL variable?"

We scored as correct the following: "Change the value of a variable by passing it as an argument to a procedure or function." Why? Because you can define an OUT or IN OUT parameter and then change the value of the variable passed as an actual argument to that parameter within the body of either a procedure or function.

Several players wrote with objections like these:

"The variable is not actually changed by the act of passing it as a parameter to a procedure or function. It is actually being modified by the assignment that happens in the body of the procedure or function. I believe that your test code for that option is actually just another example of the first option (Assign a value to a variable using the assignment syntax)."


"A variable can be assigned by passing it as a parameter to a procedure, but it cannot be assigned by passing it as a parameter to a function, so it is ambiguous whether the last option should or should not be selected."


My responses:

First, you can define OUT and IN OUT parameters in a function's parameter list, so "procedure or function" in the question does not affect the correctness of the answer. Having said that, I recommend, as do many others, that you do not include OUT and IN OUT parameters in a function's parameter list. It restricts how you can use the function (cannot call it in an SQL statement, for example), but I also believe that your functions are easier to understand, use and maintain they it only passes data back through the RETURN clause.

Second, regarding the issue of whether and when the value of a variable passed as an argument is changed: the question asked "can be assigned" [my emphasis]. The word "can" means that, and I believe is fairly commonly interpreted as, "it is possible". It does not mean that every time you pass a variable as an argument it will be changed. It does not ask you to specify the mechanism by which the change takes place.

As I read over this post, I realize that it is likely that a number of players objected because they analyzed the correctness of the choice "Change the value of a variable by passing it as an argument to a procedure or function." independently of the question. If I asked you to answer true or false to "The value of a variable is changed by passing it as an argument to a procedure." I could see why you might say "It depends."

Remember, though, that you must evaluate each choice as an answer to the question, not as an independent statement. When this is done, the "can" of the question establishes that you must only determine if such a thing can (is possible) happen.

ORDER BY needed on SELECT for answers to be correct? (2243)

The 27 April quiz focused on associative arrays and creative use of string indexing to reorder data in a collection. It prompted a flurry of emails, most of which were along these lines:

"I think the bulk select should have an order by to ensure that one of the choices is either always right or always wrong. Without the order by, I don't think you can assume the code will work in a certain way.."

"The way I read this question is which block of code _will_ show the two dates from the hire column in the order shown. The query in the pl/sql block does not contain an order by clause - there is no way to guarantee the order of the rows selected by the query. None of the blocks of code can be guaranteed to show the rows in the order shown in the question. Had the question been phrased which block of code _may_ show the specified results, then some of the answers would be correct."

"The options are effectively: 1) order by to_char(hire_date, 'J') 2) order by to_char(hire_date, 'DD-MON-RR') as NLS assumptions have a DD-MON-RR date format 3) order by to_char(hire_date, 'YYYYMMDD') 4) no order by With the date provided (inserted in the order that it is), options 1 and 3 are definitely correct, option 2 is definitely incorrect and option 4 is technically indeterminate but practically incorrect given the insert order and no indexing (based on my prior experience - ie didn't test to confirm). While it is not a actual requirement, none of the options perform a true date sort (assuming BC data, although option 1 needs pre 7-Nov-1975 BC data)."

"While in this particular case it is almost 100% sure that performing the BULK SELECT without an ORDER BY will place the 2 rows into the l_plch_employee associative array in the same order as the 2 rows were inserted into the plch_employees table, this is in general NOT ensured by SQL. Therefore, the quiz choice with AnswerID=6054, that is most probably meant to be an incorrect one, is not 100% decidable, except if you add for example an "ORDER BY employee_id" to the BULK SELECT. Just a remark for SQL-purists, that might object on this issue."

And that wasn't all! One player wrote:

"Today's quiz appeared to have no correct answer. All of these answers, except for the 3 answer, contain DECLARE...BEGIN...END blocks. These cannot be used within the BEGIN...END section of an anonymous block. The 3rd answer does nothing to specify the order of the hire dates, and is therefore inadequate."

Finally, a couple of players wrote to complain about the sheer volume of code that had to be read to answer the quiz.

My responses:

1. Regarding the lack of ORDER BY: I'd actually rather invite answers from players first, before I offer my view on this. I am curious to see what you think.

2. Regarding the inclusion of DECLARE...BEGIN...END inside another BEGIN...END: actually, that is absolutely acceptable in PL/SQL. That is an example of a nested block. I generally avoid nested blocks in my code and instead create a nested or local subprogram (procedure or function). This means that instead of the following:

BEGIN
   some_code;

   DECLARE
      l_var   NUMBER;
   BEGIN
      l_var := 100;
   END;

   more_code;
END;

I would write the following:

DECLARE
   PROCEDURE set_l_var
   IS
      l_var   NUMBER;
   BEGIN
      l_var := 100;
   END;
BEGIN
   some_code;

   set_l_var;

   more_code;
END;

I find this approach much more readable and easier to maintain.

3. Regarding volume of code: generally I try to avoid lots of code repetition in the choices by isolating the common parts, putting them in the question and just including a comment in the question code that should be replaced by the code in the choices. In this case, however, three of them were very similar but the fourth was quite different, so I could not easily avoid the situation.

Looking forward to your thoughts....SF

How did you feel about the DBMS_XA quiz? (2242)

On 26 April, the PL/SQL Challenge offered its first quiz on the DBMS_XA package. As often happens with "out of the ordinary" features or packages, I received some notes from players expressing concern.

One person called the quiz a "fiasco", adding that " honestly cannot find a decent explanation in any of the documentation."

Another wondered how a person might go about answering the quiz, posing these options:

1.  Decide from the beginning that he simply does not know enough for answering it, and simply choose  "None of the answers are correct" AND also DO THIS VERY FAST ( an option that most of the times is likely to minimize the number of wrong answers and also acquire an almost-zero time, as we already discussed in large debates several good times in the past, even recently ),  and thus "gain" a not so bad score ... surely a much better one than his real knowledge ...

OR  ( and this is really a BIG OR  !!! )

2.  Deciding to invest  ( many would rather say ... "waste" ... considering that time does count much in this competition !!! ) a certain (rather longer than small) amount of time to look a little bit into the things and trying however to come up with as good an asnwer as possible, for a maybe completely new topic for him ... and then being "punished" for the high time and scoring even lower than a player taking the first action above ...

then concluding:

How deep are we expected to have dived into these very specialized features so that to be well prepared for what is still awaiting us during the PL/SQL Challenge ... so that to be able to still continue to play it without a feeling of total frustration ...that ... I dare bet ... will be experienced by not a few players (myself included) with today's quiz ...


Here are some of my thoughts:


1. Offering a quiz every day is quite a....challenge. If I only stick with "mainstream" functionality of PL/SQL, I can tell you right now that it will be very tough to continue to offer interesting new material. I also want to leverage the knowledge of developers from around the world, who know much more about various aspects of and applications of PL/SQL than I do.


2. While I appreciate that many players are very much in "competitive mode" at the PL/SQL Challenge, the most fundamental and important objective of the site is to deepen and broaden your knowledge of the PL/SQL language (including the many built-in packages that add to the base language).

3. You will undoubtedly encounter quizzes on topics with which you have no experience. And, yes, that means that almost certainly your score for that day will not be very high. But you should try not to be terribly frustrated by such quizzes and instead focus on the learning aspects.

Your thoughts?

Define, Declare...what's the difference?

Yesterday, I received the following email:

Just answered the 20 April quiz and took a little extra time than required considering I knew this concept very well. I am just curious to know what is the difference between DECLARE and DEFINE? For me it goes like this:

Consider the block below:

DECLARE
   my_variable_declared  NUMBER;
   my_variable_defined my_variable_declared;
BEGIN
   NULL;
END;

I consider "my_variable_declared NUMBER" as DECLARE and "my_variable_defined my_variable_declared" as DEFINE.

I often feel confusion when someone talks about declare/define because I am not sure if they follow my(or standard?) convention about DECLARE/DEFINE.

Could you please comment if my assumption is correct?

I selected "You cannot declare cursor variables at the package level." as correct for this quiz but was thinking YOU CAN DECLARE but CANNOT DEFINE cursor variables at package level (per my assumption). For me "TYPE cv_t IS REF CURSOR RETURN plch_employees%ROWTYPE" is declaration of cursor variable and "emp_cv cv_t" is definition of cursor variable.

My response: I don't believe there is any "standard" - one thing I have come to learn and accept about Oracle documentation is that it is not terribly precise and it does not take the trouble to define all of its terms. I don't think that is going to change any time soon.

And I must admit that I follow in those big footsteps: I am not always terribly precise about the usage of certain terms. Regarding DECLARE and DEFINE, I would say that the term "declare" is fairly unambiguous. You declare things in the declaration section of a block (which also includes the entire package specification).

"Define" is a bit more....flexible, to my mind. I believe that I generally use that term to describe the creation ("defining") of a database object or program unit. But I probably also use it to describe the process of declaring a procedure in a package and other such steps.

In terms of the way you draw the distinction above, between a REF CURSOR type and a cursor variable, that doesn't make a whole lot of sense to me. Both are declarations, one of a type and the of a variable based on that type. But if someone showed me that package specification and told me, "Look, Steven! I defined a new REF CURSOR type!" I would immediately understand what is meant. Wouldn't you?

Other thoughts?

Infinite files? REALLY? (2205)

The 15 April quiz regarding infinite loops scored the following choice as incorrect:

PROCEDURE read_file (file_in IN UTL_FILE.file_type)
IS
   l_line   VARCHAR2 (32767);
BEGIN
   LOOP
      UTL_FILE.get_line (file_in, l_line);
   END LOOP;
END;

explaining: "This simple loop contains no EXIT, RAISE or RETURN statements, so at first glance one might think it will never terminate. This loop will not, however, go on and on and on. The reason is that there is no such thing as an infinitely large file (even if there is no limit to a file size, each and every file that is opened will have a finite number of lines in it). And when UTL_FILE.GET_LINE reads past the end of a file, it raises the NO_DATA_FOUND exception."

A player wrote with the following objection:

"The answer with UTL_FILE.get_line is not completely correct. At least the answer is stating "The reason is that there is no such thing as an infinitely large file ..." which is not correct. When I was answering it, pseudo files like /dev/urandom came to my mind, which make the loop really indefinite. If you are running on Win, you can check following wiki site: http://en.wikipedia.org/wiki//dev/random."

Big news to me. Has anyone had experience with this? Would anyone like to test out this hypothesis?

Beta 2 of PL/SQL Challenge V2 now available for review and testing

We have published a new beta version of PL/SQL Challenge 2.0.

You will notice a radically redesigned home page and welcome page (the page you see when you are not already logged in). We like it a lot, and I hope you will, too.

The home page offers an expanded scorecard, as well as quick links to recently taken quizzes and your own submitted quizzes. The Play a Quiz table is now (we believe; you tell us!) much easier to see and work with, especially given the upcoming "explosion" of multiple quizzes that you can take.

Guests (not yet registered) can now play a sample quiz and view up to five past quizzes.

The process of taking a quiz should be bug free. Please check that out.

There are, of course, a number of known issues. I suppose I could list them here, but instead you can view the current list of known issues by clicking on the Feedback button and then choosing the Known Issues report on the bottom of the page.

At this point, we are focused on cleaning up the site, getting everything working, and pushing it out for production use in early May. We welcome - and thank you in advance for - your feedback on this beta.

The beta site may found at:

http://beta.plsqlchallenge.com

You can use your regular email address and password to log in. You will see all the past quiz data available on the production site, as of 13 April.

Cheers, Steven

Yes, cursor FOR loops can be infinite loops! (2205)

In the 15 April quiz on infinite loops scored the following as incorrect (not an infinite loop):

BEGIN
   sys.DBMS_OUTPUT.disable ();

   FOR rec IN (SELECT * FROM all_source)
   LOOP
      sys.DBMS_OUTPUT.put_line ('See you later!');
   END LOOP;
END;
/

and provided the following explanation: "A cursor FOR loop will never be an infinite loop, because a query can never return an infinite number of rows."

Ah, naive Steven! Ah, Steven of the limited understanding of SQL and all of its "tricks"!

Several players wrote to say that while the choice correctly identifies a query that will always return a finite number of rows, it is, in fact, possible to craft a query that returns an unlimited number of rows. They offered these examples:

BEGIN
   FOR c IN (    SELECT dummy
                   FROM DUAL
             CONNECT BY LEVEL > 0)
   LOOP
      DBMS_LOCK (sleep (1));
   END LOOP;
END;
/

BEGIN
   FOR c IN (SELECT *
               FROM (    SELECT LEVEL
                           FROM DUAL
                     CONNECT BY LEVEL < LEVEL + 1))
   LOOP
      DBMS_LOCK (sleep (1));
   END LOOP;
END;
/

Thanks for bringing this to my attention. I will change the answer text to reflect this "nuance" of SQL.