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!
The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
01 October 2011
October SQL Quizzes by Tim Hall, ORACLE-BASE.com!
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!
29 September 2011
VALUES OF not a valuable and correct solution? (8304)
The 28 September quiz asked the following:
Several players wrote with concerns and questions, most of them having to do with this choice (scored as correct):
"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 "
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:
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.
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?
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):
"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?
27 September 2011
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:
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
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
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
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
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
26 September 2011
What's the value of using %TYPE for parameters?
I received this question from a player last week:
Rather than answer with my views immediately, I thought I'd offer it up to our players to see what you think.
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.
24 September 2011
Players report disabled RESULT CACHE...why?
The 14 September quiz tested players' knowledge of the Oracle Database 11g function result cache feature.
Two players wrote to say that their tests did not reproduce the results claimed by our verification code.
It seemed as though the result cache was not working for them. They are both running Enterprise Edition. One player reported the following:
I am at a loss to explain to these players how to get the result cache feature enabled. So I thought I would invite the PL/SQL Challenge community to offer its wisdom. Any ideas?
Two players wrote to say that their tests did not reproduce the results claimed by our verification code.
It seemed as though the result cache was not working for them. They are both running Enterprise Edition. One player reported the following:
SQL> show parameter result_cache NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 0 result_cache_max_result integer 5 result_cache_max_size big integer 0 result_cache_mode string MANUAL result_cache_remote_expiration integer 0 SQL> ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE =128M; System altered. SQL> show parameter result_cache NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 0 result_cache_max_result integer 5 result_cache_max_size big integer 0 result_cache_mode string MANUAL result_cache_remote_expiration integer 0 SQL> select dbms_result_cache.status from dual; STATUS -------------------------------------------------------------------------------- BYPASS
My Result Cache is disabled and after repeated tries, I am unable to enable it and have no clue how I can fix this.
I am at a loss to explain to these players how to get the result cache feature enabled. So I thought I would invite the PL/SQL Challenge community to offer its wisdom. Any ideas?
23 September 2011
"Could" Changes Really Improve Performance? (7755)
The 22 September quiz asked you to analyze a piece of code and decide:
Which of the choices describe a change I can make to the plch_emp_loop procedure that will not affect the external behavior of the procedure (what it displays or which rows it updates), but could improve its performance?
The quiz was intended to be about your ability to identify loop invariants, expressions within the body of the loop whose outcomes do not change with each execution of the loop. A standard optimization step is to replace the invariants with a variable (or better yet a constant!) that is not re-evaluated with each iteration of the loop.
Several players wrote with concerns about the way this question was worded and scored:
I will offer some responses here, but then open it up for discussion from players.
Which of the choices describe a change I can make to the plch_emp_loop procedure that will not affect the external behavior of the procedure (what it displays or which rows it updates), but could improve its performance?
The quiz was intended to be about your ability to identify loop invariants, expressions within the body of the loop whose outcomes do not change with each execution of the loop. A standard optimization step is to replace the invariants with a variable (or better yet a constant!) that is not re-evaluated with each iteration of the loop.
Several players wrote with concerns about the way this question was worded and scored:
1. Hi Steven, I usually do not complain about quizzes, but I felt that yesterdays quiz was a little bit vague and not up to par or standards of the plsql challenge. First thing is how the question is formed. I don't like vague words like could to be in a quiz. The should always be will, specially when it comes to performance enhancements. But looking at the question itself. "Which of the choices describe a change I can make to the plch_emp_loop procedure that will not affect the external behavior of the procedure (what it displays or which rows it updates), but could improve its performance?". What if all the data in the table have salaries less that the minimum. Then this procedure never runs and the enhancements have no effect at all anyways. 2 answer came out wrong for me, probably because I emphasized too much on the performance part of the question rather that reading the code itself. Therefore I fell into the SYSDATE trap, marking it as correct. It does improve performance, there is no doubt of that, but it of course is a changing variable and therefore will change the internal running of the program. The other one was the number 8123 "l_year NUMBER := TO_CHAR (date_in, 'YYYY');" This will make the IF statement later an implicit conversion (CHAR => NUMBER), that will not neccessarily run faster and therefore I don't think it could be a correct answer.
2. How much performance gain do you expect to get from changing from the explicit conversion TO_CHAR (date_in, 'YYYY') to the implicit conversion of l_year? I tried it, and I didn't get any significant gain.
3. Answer 3 and 4 of the 22 sept quiz contradict! On 31-12-2010 to 01-01-2011 the year changes. PS answer 3 makes an assumption about what date should be used and that changing de date is the best programming solution.
4. Option 3 was scored as incorrect: " The question did not include any constraints on when and for how long this program runs. If during the execution of the procedure, the hour changes, then we cannot safely extract the TO_CHAR (SYSDATE, 'HH24') expression and evaluate it just once at the start of the procedure." This statement is correct but I think this is not a very good example. It is a correct that the hour can change during the running of the program, but do you want to have just a part of your employees updated during a run of the program; I don't think so!
5. An answer in quiz 5142 states "The question did not include any constraints on when and for how long this program runs", but the quiz question says "never takes more than 4 hours to complete" - so I would have thought it would be safe to capture l_hour - although I guess it depends on when the program was launched. I was also curious about the affect on performance on the function call to plch_config.min_salary, since it is just an assignment, no conversion required - performance gain almost nominal?
I will offer some responses here, but then open it up for discussion from players.
- I used the word "could" because I wanted players to focus on a logical analysis of the code (what in theory could improve performance? and not so much on analyzing the actual gains of specific changes, which as some noted could be very minimal indeed).
- Excellent point about the change to l_year resulting in an implicit conversion, when previously there was no conversion. I did not catch that and I believe it makes that choice ambiguous, not clearly a net performance gain when the loop invariant is extracted. I think I will probably need to issue a correction for that choice.
- The answer for choice 3 was incorrect and has been changed to remove the leading sentence. I added time constraint text to the question, but forgot to remove this from the answer.
- I do not see a contradiction between answers 3 and 4 (8122 and 8123 to use their IDs).The year does not change while the program executes, based on " it is only executed during the first month of each quarter (January, April, July and October) and never takes more than 4 hours to complete".
14 September 2011
New Poll on Changing Question Type
The type of question for the 13 September quiz was changed from "only one choice correct" to a normal multiple choice question after the quiz was closed. This was done because a player noted the following:
And we agreed.
But another player then objected to this change, saying: "This is unfair towards the players who consumed extra time to try to answer correctly, for avoiding scoring zero points."
Our feeling is that you should be making your best effort to answer the question correctly, regardless of the type of question, and so if we make a mistake in how we set up the question, we should fix it. But we are not competing in the daily quiz, so we may not understand well how all of our players see this issue.
We have set up a poll on the PL/SQL Challenge website to find out how you feel about it. Please take the poll and/or add your thoughts to this posting.
Cheers,
Steven Feuerstein
"This is a prime example of a quiz that should not be "at most one" correct choice. More than one concept is being tested; however, if a player is weak in any part then the player receives no credit. The results of the quiz would have been more indicative of players' relative knowledge had each exception scenario been presented as an independent selection."
And we agreed.
But another player then objected to this change, saying: "This is unfair towards the players who consumed extra time to try to answer correctly, for avoiding scoring zero points."
We have set up a poll on the PL/SQL Challenge website to find out how you feel about it. Please take the poll and/or add your thoughts to this posting.
Cheers,
Steven Feuerstein
Subscribe to:
Posts (Atom)