- 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.
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: