Nuances of Nested Tables (1864)

The 13 January quiz tested your knowledge of nested tables, one of the three types of collections available in PL/SQL (two of which, nested tables and varrays, can be manipulated within SQL).

In response to this quiz, one player wrote: "I am a bit confused about nested tables. As a DBA I know that nested tables are schema objects in the database, but it what we used to call PLSQL tables are now also called nested tables? In my opinion (assuming that I am correct here, which is not necessarily the case of course), this makes today's quiz a bit ambiguous: you can use SQL statements to access nested tables that exist in the database, but not to access PLSQL tables, I believe. It is not clear which type are meant."

To clarify: the datatype formerly known as the "PL/SQL table" is now known as an associative array. It is a PL/SQL-only datatype. That is, you cannot define an associative array type as a schema level type (a.k.a, database object); you cannot use that type as a column in a relational table; you cannot manipulate an associative array with the TABLE operator - all of which you can do with nested tables and varrays. And, interestingly, even if you declare a nested table as a PL/SQL variable, you can manipulate in an SQL statement as long as the type on which it is declared is a schema-level type.

Another player wrote: "Today's quiz about nested tables is not clear. What does it mean "A nested table can have as many elements in it as a relational table has rows"? Example: I'm trying to create a straightforward column with 1001 columns of type NUMBER... it fails. I'm trying to create a straightforward table with 1 column of user type which is not final. The type has many final implementations. Once the total number of virtual columns hit 1000 it fails to insert any new data. I'm trying to create a nested table which has 1001 columns, thus 1001 elements or attributes... wouldn't it fail? Now let's assume that "element" gets redefined into "tuple" or "row". I can insert more than 1000 rows into the nested table. Finally: If I'm a stubborn being then shouldn't be the last answer true as well?"

To which I respond: an element in a collection is analogous to a row in a relational table. Which is to say that you can define and locate an element through its index value. I intended to test with the statement "A nested table can have as many elements in it as a relational table has rows" your awareness that there is an upper limit to the number of elements a nested table may contain (valid index values range from 1 to 2**31-1), while there is no such limit in an Oracle relational table.

Constant Instead of Literal a Best Practice? (1862)

In the 11 January quiz, we asked:

Which of the following statements correctly describe a way to improve the performance, readability or maintainability of this block of code?

BEGIN
   FOR month_index IN 1 .. 12
   LOOP
      UPDATE monthly_sales
         SET pct_of_sales = 100
       WHERE company_id = 10006 
         AND month_number = month_index;
   END LOOP;
END;

We scored as correct the following choice:

"Replace all hard-coded literal values with named constants or function calls."

Several players did not agree. I will offer one such comment and then open it up for discussion:

While it's generally a good idea to replace magic numbers with sensibly named constants, in the case of month names I feel the month number itself is a very well readable and commonly used name (as in "Quiz for 2011-01-11 Tuesday"). And did you know that even in German we have two names for the first month, namely "Januar" and "JÃnner"? But why is this "AND month_number BETWEEN 1 AND 12" clause there anyway? Unless someone invents more months (e. g. "Tricember") all months in any real world table may be assumed to range between 1 and 12 unless they are null. So I would probably change the statement like UPDATE monthly_sales SET pct_of_sales = percent_in WHERE company_id = company_id_in Yet another point is binding: If you introduce constants, they will be bound where they are used in SQL statements. The literals won't. Binding constants is probably not as good an idea as binding variables. Well these are just my 2 cents.

Ah - one other thing: another person objected to scoring this choice as correct because he feels it would WORSEN performance (I will leave it to the player to post his comments here). Even if that were true, the question uses the word "or" not "and" - so as long as replacement of literals with constants satisfies improved readability or maintainability, it does NOT have to improve performance.

So, dear players, what do you think?