Questions raised about 29 July quiz(1281)

Yes, the questions and suggestions that I have made another mistake just keep on rolling in! But this time, I am pretty sure the quiz is error free and no scoring is required. The questions were quite interesting and reflected, for the most pat, the complexities of dynamic SQL, especially dynamic PL/SQL. The quiz for 29 July asked you to choose an implementation for this header:

PROCEDURE show_value_with (
   show_program_in IN VARCHAR2
 , string_in IN VARCHAR2)

One person wrote that "execute immediate SQL statements should not end with semicolons" - and that is true of dynamic SQL statements. Dynamic PL/SQL statements, which is what was needed to solve this problem, must end with a semi-colon, as in:

IS
  plsql_stmt VARCHAR2 (1000)
        := 'BEGIN ' || show_program_in || '(:value); END;';
BEGIN
  EXECUTE IMMEDIATE plsql_stmt USING string_in;
END;

Several other players wrote that "There's a minor inconsistency between the question that says the string is "not more than 10,000 bytes" and the answers that have plsql_stmt defined as varchar2(1000) - 10 times less. I think it's a typo." You can see an example of what they mean in the previous block of code - but they are not correct; there is no problem here. In the two choices that declare a variable of length 1000, the string that is to be displayed is not concatenated into the plsql_stmt variable. Instead, that potentially long string (longer than 1000 anyway) is bound into the PL/SQL block with the USING clause. Thus, there is no problem regarding the length of the variable. Another person wrote: "For today's quiz I must say that for the code snippet to work, dbms_output.enable(10000); should be there for buffer allocation else desired effect wont be there. dbms_output has some limitation of around 256 bytes. This is for those which breach this limit . In current scenario, 'ABC' is just 3 chars hence it should work. Anything above limitation will fail and hence it has to be either enabled for the session prior to execution. In absence of anything, we can't assume this and hence in that case all the answers would be wrong. Else there are some right answers and i think i have marked all or some of them correctly. Now, this player has a point. We state in the assumption that server output is enabled, but we do not specify the buffer size. I will add a "buffer unlimited" clause to the assumptions in our next release (1.5). But since Oracle10g Release 2 did, in fact, introduce the unlimited buffer option, my feeling is that is genreally no longer an issue in the PL/SQL development and testing environment. I am not going to change the scores or ranks for this reason. Finally, a number of players wrote to me along these lines: "In 29th July quiz question the procedure name is mentioned as show_value_with but in the answers it is referred as show_program_in." This is not true. The name of the procedure that executes the dynamic PL/SQL block is show_value_with. The name of the parameter to this procedure is show_program_in, and it contains the name of the procedure to be executed inside the dynamic PL/SQL block. No doubt about it, working with dynamic SQL and especially dynamic PL/SQL can be very tricky. It can be hard to keep it all straight when there is more than one level of evaluation going on. But I did manage to get it right, which I felt fairly confident of, seeing as I wrote and ran a script to verify each answer. I look forward to your comments on this quiz! SF

Questions regarding 28 July 2010(1251)

The 28 July 2010 quiz tested your knowledge of a really fantastic Oracle11g feature: the function result cache. First, a few players questioned why we offered a quiz on an Oracle11g topic, when in our assumptions we state that you should assume an Oracle10g Release 2 database instance. That is an assumption. In other words, unless stated otherwise, you assume a 10.2 database. But that doesn't mean we won't offer quizzes regarding a different version of the database (or a Unicode/multibyte character set question or...). If, on the other hand, I offer a quiz that relies on an 11g feature and I do not explicitly state that you are working with Oracle11g, you have every "right" to report that as a mistake in the quiz. We will soon add some text to the Assumptions section to make this more explicit. Second, a few players raised concerns along this line: a result cache function "can return records but not all types of records" (such as a record that has as one of its fields an object type). The quiz question asks "Which of the following types of data can be used in the parameter list or return clause (but not necessarily both) of a function defined using the RESULT_CACHE keyword?" And we scored as correct "Records (user-defined and %ROWTYPE)". I don't believe this requires any change or re-scoring. You can, in fact, use a record type in the return clause of a result cache function. No, you cannot use every single kind of record type, but I did not ask that and I don't think it is reasonable to interpret that this choice means that Oracle will support every type of record structure. Third, a player wrote "Isn't yesterday's quiz is confusing? How to interpret "(but not necessarily both)". What the user has to select in the answer, 1. Can be put in Parameter? 2. Can be returned? How one should interpret?" I am sorry if this construction caused any confusion. I simply wanted to emphasize that a correct answer could be one in which the datatype is support in the parameter list, but not as a return type, or vice versa. I suppose that this clause was unnecessary, since I had already used the OR operator in my sentence. :-) But I would like to think there is no ambiguity there. I look forward to your thoughts. SF

Clarification regarding assumptions for quizzes

Each day, before you take a quiz, we show you the general assumptions for the quizzes. This includes that we are assuming all quizzes execute on a 10.2 instance. An assumption means that unless we mention anything otherwise, you assume that configuration. If, in the quiz question, we explicitly state something that differs from an assumption (like "On Oracle 11g") then the question takes precedence over the assumption. In other words, just because we state that you should assume 10.2 (unless otherwise mentioned), we do not exclude the possiblity of a question on 11g appearing in the daily quiz.

In our next release (1.5), we will add text to this page making this point more explicit.

"Extraneous Info" strikes again - mistake confirmed for 27 July 2010(1261)

Two choices in the 27 July quiz included this phrase "in place of a table or view name." As many players noted, you cannot call a function "in place of" the name of a table or view. Instead, the function invocation must be placed inside a TABLE operator, as in TABLE (my_function()). Thus, two of the choices were scored incorrectly. We have changed the text of the answers to remove this "in place of" clause. We then gave 931 players credit for a correct answer for those two items, and rescored/reranked. AL0 wins an O''Reilly Media ebook for being the first to identify this problem. In this case, one of my reviewers actually noted this problem, but I did not read his critique closely enough and thought that he was referring to something else. Argh.... Regards, Steven Feuerstein

Players question validity of a choice in the 26 July quiz(1249)

A half dozen players submitted the same question regarding the 26 July quiz, which asked: "Which of these blocks, when executed, display the names of tables owned by the schema to which I am currently connected?" One of the choices, which we scored as correct, offered a query against the USER_TAB_COLUMNS data dictionary view. Players objected, because the query of DISTINCT TABLE_NAME from this view would return the names not only of tables, but of views, as well. Here's one player's comment: "I did not select distinct table from from user_tab_columns option because it would also show you column names from views in the connected schema. The wording of the question could be taken either way --- it did not specify "only" tables, but it could be readily interpreted to mean that. In this case, I thought the user_tab_columns answer was a poor selection as it did not yield the precise answer the question was asking." While I can understand that this scenario would raise a question in the minds of some players, I must stand by the scoring of this choice as correct. The result set does display the names of tables owned by the schema. If the question had asked you to choose those blocks of code that display only the names of tables, it would clearly be incorrect.