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