28 May 2010

When is a table JUST a table? The danger of making assumptions(324)

On 27 May 2010, the PL/SQL Challenge quiz question was: "Which of the following choices show the header of a function that accepts as its only parameter (of type IN) a record with the same structure as the from_table table and returns through its RETURN clause a record with the same structure as the to_table table?" [To see the full details of this quiz, visit the Previous Quizzes page] One player wrote the following comment: "There is no mention in the quiz whether it's a database table or a nested table. In case of nested table, the option 3 is also correct." I commend this player for even knowing about nested tables. Many developers have very limited familiarity with PL/SQL collections (which is a problem - you cannot take full advantage of the most important features of PL/SQL without using collections). One the biggest, ahem, challenges of the PL/SQL Challenge to date has been to avoid making assumptions in the question that then lead to ambiguity in the answers. You'd think that "PL/SQL is PL/SQL" and it wouldn't be hard to write Q&As that are entirely unambiguous.If only it were that simple. While PL/SQL syntax is what it is, PL/SQL code is executed in widely varying environments. Variables include the Oracle version (of course), the operating system of the database server, the edition of the database, the character set used, the date format, and so on. As a result, our list of assumptions, of which we remind players each day they play the quiz, has grown markedly in the last two months. It now states:
  • The database version is 10.2 (Oracle Database 10g Release 2); the edition is Enterprise Edition; the database character set is an 8-bit character set; and the national character set is AL16UTF16.
  • The PL/SQL compiler optimization level controlled through the plsql_optimize_level initialization parameter is set to 2, and all PL/SQL code is compiled without debug information.
  • The session and the environment in which the quiz code executes has enabled output from DBMS_OUTPUT, and can reference only those datatypes, programs and database objects defined in the context of the quiz or are available in a default installation of the Oracle instance.
If you believe, by the way, that any important assumptions have been left out, please notify us through the "Report Bug/Comment" link at the bottom of every page on the site. Having said that, I do not think it should be necessary to qualify the word "table" in the context of PL/SQL programming. A table is a database object that contains a set of rows and columns of data. A nested table is a kind of collection, but a nested table is not a kind of table. An unqualified reference to "table" cannot reasonably be thought to include any type of collection. What do you think? Steven Feuerstein
Posted by at

3 comments:

  1. Wim de Lange31 May, 2010 07:23

    I agree on the fact that the term "table" refers to the object that is sitting in a database without any special properties (like temporary tables). It is like object oriented languages. You have the generic term, and you have specific terms that look like the original object but could be implemented and behave in a complete different manner.
    I must admit this is a hard discussion. But if you must specify exactly what you mean in a challenge, then they will become unreadable and are not fun anymore todo.

    ReplyDelete
  2. Filipe Silva31 May, 2010 09:53

    A Table in DB context for me its evidently a database table (I also do not think it should be necessary to qualify the word "table" in the context of PL/SQL programming).
    A Nested Table is a collection, like associative arrays (formerly called PL/SQL table :-))

    ReplyDelete
  3. Ramesh02 June, 2010 03:16

    The quiz questions are often tricky and there are many possibilities in answers, like all the options may be correct or none of them.

    Considering this situation, it happens sometimes that if you don't correctly interpret the question, you may select wrong choices even if you know the answer and concept behind the question. So it is obvious for any player to take extra precaution and derive different meanings from a question when he/she sees an option matching close to the answer to question. That is what happened here.

    I am in total agreement that TABLE means a database table (of course) but PL/SQL TABLEs (associative arrays) are treated much similar to real database tables in some cases like "when referenced element does not exists in an associative array, NO_DATA_FOUND exception is raised".

    ReplyDelete

Subscribe to: Post Comments (Atom)