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

Well, the PL/SQL Challenge isn't JUST a game!

We recently received this email and thought you'd enjoy it as much as we did:

Before ending my work day at the office, I decided to play my daily quiz, and was surprised by the following message:
------------------------------------------------------------------------------------------------------------------
You cannot access the following Web address: http://plsqlchallenge.com/
 
The site you requested is blocked under the following categories: Games
  
You can: Submit a site review request to your network administrator.
------------------------------------------------------------------------------------------------------------------
I did write to the network administrator, hoping that someone will review this classification. Sad huh? No more PL/SQL discussion between the developers here at work...

We sure hope he gets that sorted out, and that none of you are stopped by your network administrator or (even worse) a narrow-minded manager from playing the PL/SQL Challenge!

26 May 2010 quiz: UTL_FILE and newline character: were we wrong?(323)

Dozens of players have questioned the validity of several of the answers we have marked as correct for the quiz on 26 May 2010.

We've seen two different objections:

1. 1. "The answer using CHR(10) is not correct as CHR(10) is not the platform-specific line terminator(s) on Windows and some other OSs".

2. 2. "The answers that put a newline after "abc" are not valid answers according to your specification."

We'll answer each objection in turn.

CHR(10)

We can certainly understand the response we got, since we felt the same way when it was initially brought to our attention. At that point, however, we took a closer look and discovered some very interesting behavior by Oracle and UTL_FILE regarding the handling of line terminators. It is correct that CHR(10) (LineFeed or LF) is not the platform-specific line terminator(s) on Windows (that would be CHR(13) || CHR(10) -- CarriageReturn + LineFeed or CF + LF) but the file is opened in text mode (default) so Oracle translates any LF to the platform-specific line terminator(s) on the platform of the host. So if it runs on Windows, LF is translated to CR + LF, if it runs on Linux, LF stays as LF.

This can be verified on Windows through the following code sample (assuming that the directory TEMP points to c:\temp):

host del c:\temp\quiz.txt

DECLARE

l_file UTL_FILE.file_type;

BEGIN

l_file := UTL_FILE.FOPEN('TEMP', 'quiz.txt', 'W');

UTL_FILE.PUT (l_file, 'First' || chr(10));

UTL_FILE.PUT (l_file, 'Second' || chr(13) || chr(10));

UTL_FILE.PUT (l_file, 'Third' || chr(10) || chr(13));

UTL_FILE.PUT_LINE (l_file, 'Fourth');

UTL_FILE.PUTF (l_file, 'Fifth\n');

UTL_FILE.FCLOSE (l_file);

END;

/

host debug c:\temp\quiz.txt

If you're on Windows Vista or newer, debug is no longer part of your Windows version. ). To get a hex dump of a file under Windows one can use a free utility called HexDump32:

http://hexdump32.salty-brine-software.qarchive.org/_download2.html

This will demonstrate that the contents of the file is actually (OD = CR, 0A = LF):

00000000 46 69 72 73 74 0D 0A 53-65 63 6F 6E 64 0D 0D 0A First..Second...

00000010 54 68 69 72 64 0D 0A 0D-46 6F 75 72 74 68 0D 0A Third...Fourth..

00000020 46 69 66 74 68 0D 0A - Fifth..

So when a file is opened in text mode, UTL_FILE does this:

1. First: CHR(10) is written as OD OA, CHR(13) + CHR(10).

2. Second: CHR(13) || CHR(10) is written as OD OD OA, CHR(13) + CHR(13) + CHR(10).

3. Third: CHR(10) || CHR(13) is written as OD OA OD, CHR(13) + CHR(10) + CHR(13).

4. Fourth: PUT_LINE correctly puts OD OA, CHR(13) + CHR(10).

5. Fifth: PUTF correctly puts \n as OD OA, CHR(13) + CHR(10).

On Linux, you can similarly verify the behavior (assuming that the directory TEMP points to /tmp):

host rm /tmp/quiz.txt

DECLARE

l_file UTL_FILE.file_type;

BEGIN

l_file := UTL_FILE.FOPEN('TEMP', 'quiz.txt', 'W');

UTL_FILE.PUT (l_file, 'First' || chr(10));

UTL_FILE.PUT (l_file, 'Second' || chr(13) || chr(10));

UTL_FILE.PUT (l_file, 'Third' || chr(10) || chr(13));

UTL_FILE.PUT_LINE (l_file, 'Fourth');

UTL_FILE.PUTF (l_file, 'Fifth\n');

UTL_FILE.FCLOSE (l_file);

END;

/

host hexdump –C /tmp/quiz.txt

Why does Oracle do this? Probably because UTL_FILE is implemented in C and this is how it's done in C's functions fopen and fprintf when a file is opened in text mode. Admittedly, it's not well described in the Oracle documentation.

You can also open files with UTL_FILE in byte mode but in this case you can only use procedures PUT_RAW and GET_RAW. If you use any of the other subprograms UTL_FILE will raise an UTL_FILE.INVALID_OPERATION exception. With byte mode, Oracle doesn't do any translation of newline characters, which is demonstrated in the following (to be run under Windows):

host del c:\temp\quiz.bin

DECLARE

l_file UTL_FILE.file_type;

BEGIN

l_file := UTL_FILE.FOPEN('TEMP', 'quiz.bin', 'wb');

UTL_FILE.PUT_RAW (l_file, utl_raw.cast_to_raw('First' || chr(10)));

UTL_FILE.PUT_RAW (l_file, utl_raw.cast_to_raw('Second' || chr(13) || chr(10)));

UTL_FILE.PUT_RAW (l_file, utl_raw.cast_to_raw('Third' || chr(10) || chr(13)));

UTL_FILE.FCLOSE (l_file);

END;

/

host debug c:\temp\quiz.bin

This demonstrates the following contents:

00000000 46 69 72 73 74 0A 53 65-63 6F 6E 64 0D 0A 54 68 First.Second..Th

00000010 69 72 64 0A 0D - ird..

Newline after "abc"

We argue that the line terminator (= newline) is part of the line (like an End Of File is part of a file so is End Of Line part of a line) so as long as "abc" is put on a separate line (with optional line terminator), coming after 2 empty lines (with line terminator) the code meets the specification.

Conclusion

For all the reasons stated above, we contend that the quiz of 26 May 2010 is correct; scores and ranking will remain "as is."

Welcome to the PL/SQL Challenge blog!

We (Finn Ellebaek Nielsen and Steven Feuerstein, creators of the PL/SQL Challenge) have decided to start up a blog to both chronicle the goings-on of the PL/SQL Challenge.

We will post entries here when players raise questions about a particular quiz, providing an opportunity for players to discuss that quiz.

I (Steven Feuerstein) also plan to offer some background as to why I initiated the PL/SQL Challenge project and where I intend to go with it.

We hope that if you are a PL/SQL developer or, more generally, an Oracle technologist who works with PL/SQL, that you will play the PL/SQL Challenge and help us build a world-wide community of engaged, ever-more-expert PL/SQL programmers.

Happy coding!
Steven Feuerstein and Finn Ellebaek Nielsen