October SQL Quizzes by Tim Hall, ORACLE-BASE.com!

 

As a special treat to our players, Tim Hall of ORACLE-BASE.com fame is providing the SQL quizzes during the month of October. "Oracle ACE of the Year 2006" Tim Hall is an Oracle DBA/Developer and well known Oracle blogger at http://www.oracle-base.com. If that isn't enough, he's written two books and over 400 articles, is an Oracle ACE Director, has presented at numerous user groups and conferences worldwide and recently presented a series of 2-day PL/SQL tuning seminars in Europe and Asia-Pacific as part of the "Oracle University Celebrity Series."

We are extremely pleased to have Tim offer his expertise and teaching skills to the PL/SQL Challenge for the month of October. We hope you enjoy - and are challenged by - Tim's quizzes!

VALUES OF not a valuable and correct solution? (8304)

The 28 September quiz asked the following:

I must write a procedure that accepts an associative array as a parameter, each of whose elements contains the primary key of a row in that table, and then uses FORALL to update each row identified by one of those primary keys. The associative array may not be densely filled (in other words, there may be index values between the integers returned by the FIRST and LAST methods that are not defined). Which of the choices describe my options for implementing this procedure?

Several players wrote with concerns and questions, most of them having to do with this choice (scored as correct):

Use the VALUES OF clause with the FORALL statement.
Let's address that first, and then touch on the others. Players wrote as follows:

"I set the 'VALUES OF'-answer to wrong because this clause needs and only works with indexes of type PLS_INTEGER. The documentation says: "The index collection must be a nested table, or an associative array indexed by PLS_INTEGER or BINARY_INTEGER, whose elements are also PLS_INTEGER or BINARY_INTEGER." You said nothing about type of the index of the associative array, so I thought there are some cases where this solution does not work "

"The last answer, using the "values of clause" is scored as correct, but you need to write some extra code to populate a second collection. Using the same logic the second answer, You will not be able to use FORALL, would be valid choice too. Isn't it?"

"Hello, I don't agree with the scoring of this quiz for the choice 4 ("Use the VALUES OF clause with the FORALL statement.") - I thought this was a trick question since FORALL ... VALUES OF ... cannot be used without the second collection, which was not mentioned in the answer, whereas FORALL ... INDICES OF ... can be used directly on our collection. It would have been better if the answer for choice 4 had some mentioning of the second collection (although it would have hinted that it was a correct answer). Based on the first choice, I expected the wording for the fourth choice to be something similar ("Before executing the FORALL statement populate a second collection with the index values placed in its elements. Then use the VALUES OF clause with the FORALL statement"). Actually, this is not a score adjustment request, just a comment."

My thoughts: I suppose this is yet another example of the difficulties one may encounter with an "all words" quiz (as oppose to one based on specific code). The objective of the quiz was to make sure developers are aware of INDICES OF and VALUES OF as FORALL clauses that allow you to reference a sparse bind array in the FORALL's DML statement.

The question does not specify any restrictions on how the procedure will implement the use of FORALL. It does not say that no other code can be written, no other collections can be defined and used. So from that standpoint, almost any choice (except something on the order of "You will not be able to use FORALL in this scenario.") would need to be scored as correct. Which we did.

So I do not see why the VALUES OF choice would need to be scored as incorrect.

Other comments and my responses:

"1. The question refers to "that table" but didn't mention a table beforehand. 2. The word "element" might be misinterpreted as referring to either the indexes or the values in the associative array."

My response: yes "that table" was a typo. I will fix it, but I don't think it justifies a rescore. Element and index values: these two terms are used by Oracle in its documentation. An element is never used to refer to the index value, that I know of.

"In the PL/SQL Quiz from 2011-09-28 you stated:"I must write a procedure that accepts an associative array as a parameter, each of whose elements contains the primary key of a row in that table...". This is a litle bit confusing for me, because I took into account the possibility, that primary key may also consist of multiple columns, not only single column. Unfortunately, the question was for me not so clearly defined... Please, could you explain, which solution would be correct, if there is a multiple columns primary key"

My response: another very excellent point regarding the potential drawbacks of an "only words" quiz! Yes, I did not think about multiple column primary keys. That would certainly affect and complicate the implementation of the procedure (assuming it was a collection of records, you would have to write very different code in pre-Oracle11g versions, since you cannot reference a field of a collection-based record inside a FORALL until then), but my same point applies from above: I do not specify any restrictions on how the procedure is implemented, except that it use FORALL. So the correctness of choices would still not change.

Well, that's my view on the 28 September quiz. How about you?

Recent Quizzes from the PL/SQL Challenge

In the month of September (to date), over 1260 Oracle technologists submitted 14,360 answers to quizzes, and explored the library of past quizzes. Here is a selection of quizzes from this month:

Oracle PL/SQL Quizzes

12 September 2011: You can trap an exception by name or you can trap it with WHEN OTHERS. If you know which exceptions might be raised, it is generally better to include a handler for that specific exception, usually so that you can also log more specific information about what might have caused the error.
610 Players ** Avg. Correct: 88% ** Avg. Time: 200 seconds ** Rating: 4 stars

13 September 2011: When an exception is raised in the declaration section (such as by assigning a too-long string as the default value for a variable), the exception propagates out unhandled from that block - even if there is an exception section and OTHERS clause.
638 Players ** Avg. Correct: 37% ** Avg. Time: 320 seconds ** Rating: 4 stars

14 September 2011: If the body of your result cache function depends on settings that might vary from session to session (such as NLS_DATE_FORMAT and TIME ZONE), make the function result-cached only if you can modify it to handle the variations in settings that might occur.
612 Players ** Avg. Correct: 69% ** Avg. Time: 259 seconds ** Rating: 4 stars

15 September 2011: When comparing values that may be NULL, pay close attention to handling all possible cases, to ensure that the correct Boolean value is returned.
604 Players ** Avg. Correct: 65% ** Avg. Time: 212 seconds ** Rating: 4 stars

16 September 2011: In case a dynamic SQL statement makes repeated use of a placeholder name, it is necessary to provide a bind value in the USING clause for each occurrence. In the same situation, when executing a dynamic PL/SQL block, only one value should be specified for each placeholder name, even if it is used several times.
576 Players ** Avg. Correct: 64% ** Avg. Time: 220 seconds ** Rating: 4 stars
19 September 2011: When writing complex Boolean expressions with multiples ANDs and ORs, remember that AND takes precedence over OR. Use parentheses to remove any possible ambiguity in your understanding of how the statement will be evaluated.
595 Players ** Avg. Correct: 94% ** Avg. Time: 156 seconds ** Rating: 4 stars

20 September 2011: Use CURRENT OF with FOR UPDATE to avoid writing or repeating unnecessary logic in a WHERE clause for DELETEs and UPDATEs inside your loop.
647 Players ** Avg. Correct: 88% ** Avg. Time: 278 seconds ** Rating: 4 stars
21 September 2011: You cannot define a nested block as an autonomous transaction. If you need that behavior, simply create a nested procedure within the nested block, and then define that procedure as an autonomous transaction.
634 Players ** Avg. Correct: 72% ** Avg. Time: 343 seconds ** Rating: 4 stars

22 September 2011: Make sure that any expressions executed within a loop must be re-evaluated with each iteration of the loop body. If they do not change (they are "loop invariants"), then execute the expression once, assign the outcome to a local variable, and reference that variable inside the loop.
610 Players ** Avg. Correct: 65% ** Avg. Time: 400 seconds ** Rating: 4 stars

23 September 2011: When writing complex numeric expressions involving several different types of operators, make sure that you both understand the precedence by which the operators are applied and that you use parentheses to ensure that the expression is evaluated as desired.
589 Players ** Avg. Correct: 91% ** Avg. Time: 123 seconds ** Rating: 4 stars

SQL Quizzes

Here are the SQL quizzes played in September: 3 September 2011: Use of analytic function COUNT with the RANGE clause to count records with values within defined ranges.
520 Players ** Avg. Correct: 82% ** Avg. Time: 2082 seconds ** Rating: 4 stars

10 September 2011: Partitioned outer join is one of the most useful data densification techniques available since Oracle10g.
526 Players ** Avg. Correct: 61% ** Avg. Time: 4645 seconds ** Rating: 4 stars

17 September 2011: Use of analytic function RATIO_TO_REPORT to show "percent of total" in select statements.
474 Players ** Avg. Correct: 72% ** Avg. Time: 2931 seconds ** Rating: 4 stars

APEX Quizzes

Here are the APEX quizzes played in September:

3 September 2011: This question will teach users how the APEX data dictionary views can be used to find out information about the applications in the workspace.
223 Players ** Avg. Correct: 59% ** Avg. Time: 925 seconds ** Rating: 4 stars

10 September 2011: Shows different methods of customising error messages inside APEX that will save time repeating code.
199 Players ** Avg. Correct: 56% ** Avg. Time: 208 seconds ** Rating: 4 stars

17 September 2011: Learm how to correctly send emails using the apex_mail.send procedure.
206 Players ** Avg. Correct: 66% ** Avg. Time: 2206 seconds ** Rating: 4 stars

What's the value of using %TYPE for parameters?

I received this question from a player last week:

This is not a question about the 13 September quiz in particular, but a related issue that it raises in my mind. I know that I should never do this:

l_myval VARCHAR2(100);

when I can do this:

l_myval mytab.col1%TYPE;

But what about parameters? Until Tuesday's quiz, I assumed that the constraints on subtypes and anchored parameter would be checked. Now I see that they're not. So is there any advantage in writing

PROCEDURE myproc(p_in IN mytable.col1%TYPE) 

instead of:

PROCEDURE myproc(p_in IN VARCHAR2)

? The only advantage that I can see is that the first version might make it more obvious how the procedure is intended to be used.

Rather than answer with my views immediately, I thought I'd offer it up to our players to see what you think.