31 March 2012

It's AMIS Month for the SQL Quiz!

I am very pleased to announce that the SQL quizzes for the month of April are provided by Lucas Jellema and Patrick Barel of AMIS (you will find at the end of this post a description of AMIS). Patrick is a longtime player at the PL/SQL Challenge, and also both reviews and authors SQL quizzes. Lucas is Chief Technology Officer of AMIS and a well-known and highly-regarded presenter at numerous Oracle conferences.

I hope that you enjoy their SQL quizzes. My deepest thanks to AMIS for supporting the Oracle community through the PL/SQL Challenge site.

Steven Feuerstein

About AMIS

AMIS is an Oracle, Java and SOA Specialist from The Netherlands, founded in 1991. Consistently active as frontrunner with the latest Oracle technology, AMIS is well known around the world for its community contributions, through presentations on conferences, Master Classes, articles in developer magazines, books and its Technology Blog (http://technology.amis.nl). AMIS has 5 Oracle ACEs and ACE Directors among its staff.

AMIS provides consultancy services around Oracle technology, including Database Development (SQL, PL/SQL, APEX); User Interface development using Java/JEE,ADF and WebCenter; SOA & BPM using SOA Suite, BPM Suite and OSB; BI using ODI, OBI EE; Security, Performance, Scalability, Availability and Administration of run time infrastructures including Database, Middleware and VMs. These services are rendered locally in The Netherlands, but also to customers in other countries and continents. Note that the Oracle Database, SQL and PL/SQL have always been crucial in the activities of AMIS.

AMIS is probably best known for its advanced knowledge, experiences and specialists. Knowledge transfer in various forms - training, coaching, reviews, "education permanente" - is a key element of what the company does. AMIS for example can deliver masterclasses and trainings on site (or through webinars) and also offers self-study materials.

The latest initiative from AMIS is the 'further' training program, targeted at experienced Oracle professionals, who are somewhat stuck in traditional tools and technology and perhaps yesterday's versions or ways of working, and who are now ready and eager to 'further' their knowledge and career. This program is offered to individual developers as well as to organizations who have teams to propel in this fashion. The program is provided in various ways and through various channels.

More information about AMIS can be found at the corporate web site, the Twitter account - @AMIS_Services, its technology blog, and through email to info@amis.nl.

29 March 2012

A Hierarchy of One? (11633)

The 28 March quiz on object type hierarchies asked players to identify those keywords that "that are needed to create an object type hierarchy". Several players wrote to object to the way the quiz was scored:

1. "The question for this quiz was: Which of the choices are keywords that are needed to create an object type hierarchy? (with the word "hierarchy" in bold letters). You have marked the answer "CREATE TYPE" ([ 10065 ]) as correct with explanation "We use the CREATE TYPE statement to create an object type, so this is definitely needed!". Of course, it is obvious that you cannot create a type without CREATE TYPE keywords, but with "hierarchy" being bolded in the question I concluded that we only need to mark the keywords that are strictly used for creating object type hierarchies. That's why I left this answer unmarked. I think you should mark this answer as correct for everyone because of ambiguity in the question."

2. "In my opinion the keyword "INSTANTIABLE" is needed for at least one class because otherwise you can't instantiate an instance of any object in your hierarchy."

3. " I do not agree with your evaluation of the right answers. You have NOT mentioned that the hierarchy is supposed to have AT LEAST two levels. It is true that a hierarchy with only one level is trivial, but it is still a hierarchy. For such trivial hierarchies the first two choices are not mandatory. This is like the empty set in mathematics. It is still a set even it has no element. It is trivial but exists!"

And my responses:

1. I don't think you can say "Of course" I am correct, but then believe that the choice is not correct. I understand your concern about the word hierarchy being bolded, but it doesn't take away from the fact that you need CREATE TYPE to accomplish this.

2. INSTANTIABLE is the default for an object type and method, so I don't see why this would be needed.

3. While your objection may be theoretically correct, I think that it is plenty clear from the emphasis on hierarchy that the intention of the question was to identify the keywords needed to create a non-trivial hierarchy.

I do not believe that any change in scoring is required. Your thoughts?

Cheers, Steven

28 March 2012

Ah, Those Edge Cases! (11632)

The 27 March quiz tested your knowledge of collections, and the kinds of assumptions you might make when writing a program that accepts a collection as a parameter.

The way the quiz was formulated was different from the usual, and it definitely caused some frustration (one player wrote "Maybe it is because I was awake for about 2 hours last night doing some DBA work for a client, but I did not even understand the question that was being asked. Even after looking in the Library to see the Topic/Summary, my co-worker and myself are still confused on what exactly was being asked." and another: "This question was confusing. I assumed that the requirements were the requirements of the code that was being called. In other words, if all the conditions are met the code will work properly, not does this code enforce all the conditions.").

Our very smart and dedicated reviewers caught many issues with this quiz, and we spent a fair amount of time discussing ways to tighten up the quiz (and I followed almost all of their advice). Yet, in the end, we were "defeated" (that is, an issue was identified in the way the quiz was scored) by an edge case regarding string-indexed arrays - which was covered in the 22 March quiz.

Several players pointed out that choice 9991 (the choice that was scored as correct and stated that no requirements applied) would require an integer-indexed array, since the algorithm would not correctly display all elements in the collection if one of the index values was the empty string.

How right they are, as one player demonstrated with a script:
SQL: CREATE OR REPLACE PACKAGE plch_pkg AS
  2     TYPE my_collection IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(20);
  3     index_type VARCHAR2(20);
  4  END plch_pkg;
  5  /
 
Package created

SQL: CREATE OR REPLACE PROCEDURE plch_proc(numbers_in IN plch_pkg.my_collection) IS
  2     l_index plch_pkg.index_type%TYPE := numbers_in.FIRST;
  3  BEGIN
  4     WHILE (l_index IS NOT NULL) LOOP
  5        DBMS_OUTPUT.put_line(numbers_in(l_index));
  6        l_index := numbers_in.NEXT(l_index);
  7     END LOOP;
  8  END;
  9  /
 
Procedure created

SQL: DECLARE
  2     l plch_pkg.my_collection;
  3  BEGIN
  4     l('b') := 'b';
  5     dbms_output.put_line('without null index');
  6     plch_proc(l);
  7     l('') := 'a';
  8     dbms_output.put_line('with null index');
  9     plch_proc(l);
 10  END;
 11  /
 
without null index
b
with null index
 
PL/SQL procedure successfully completed
Another players also wrote to say that "There is a requirement that the collection be non-empty, but no requirement that the collection is initialized (relevant for nested tables and varrays)." This is true. We did not include as a requirement in the list the need for a collection to be initialized. And we did not specify that you should only consider the requirements in the list.

What's a quiz administrator to do?

1. My biggest problem with this quiz is the possible complications for future quizzes. It is one (interesting) thing to know that an empty string as index value can cause odd behavior. It is quite another thing to have to account for this scenario in every quiz on string-indexed arrays. Seems like an unnecessary complication. So I plan to add to the assumptions for the daily PL/SQL quiz that unless otherwise mentioned, all index values of string-indexed collections have a non-NULL length. Any objections to my doing this?

2. Add to this quiz a statement to the effect that only those requirements listed in the question are to be considered. That is, there might be other requirements (such as "The nested table or varray must be initialized.") that could apply, but are not to be considered in this quiz.

3. Give credit for a correct answer on 9991 to all players who submit feedback indicating they marked this choice as incorrect because of the possibility of an empty string index value. I very much doubt that many players recalled this edge case and applied it to this quiz, but if you did, let us know and we will give you credit.

4. Give credit to any player who marked all choices as incorrect and submit feedback indicating that they did so because we did not include a requirement specifying initialization of nested tables and varrays. I say "all choices as incorrect" because if you thought of this additional requirement, then none of the choices could be correct (only the first even includes the "must be associative array" requirement).

Did I miss anything?