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.

14 October 2011

No Error Log Table, So No Choices Correct? (7990)

The 13 October quiz tested your knowledge of the DBMS_ERRLOG package and what happens when you try to create an error logging table for a table that has unsupported column types, such as CLOB.

Several players wrote with objections along this line:

I disagree with your answer to this quiz. You say that ORA-00942... propagates unhandled from the block. While this is true, I still selected 'none of the answers are true' because before this error shows, you get the following error when you attempt to execute the create_error_log statement: 'ORA-20069: unsupported column type(s) found: CLOB_VALUE....' so in my opinion, none of the answers are correct.

The question stated "I execute the following statements:" and indeed the execution of this block:

will result in ORA-20069 being raised. But we never stated that each of these statements ran without error, only that we ran them.

Then, because the error logging table was not created, the attempt to use LOG ERRORS with the DML statement causes ORA-00942 (table or view does not exist) to be raised.

My main objective with this quiz was to make you aware that you could get a very puzzling error (table or view does not exist) from a DML statement against a table that clearly does exist. It is not in any way obvious that the error has to do with the fact that the underlying, unnamed error logging table does not exist.

But if you agree that "this is true" - that (to repeat the choice we scored as correct):

17 comments:

At first I was puzzled by the error, too, but after executing the last block and getting the error described in answer 2 I decided to go for that choice - the error before DID NOT interfere with the last block, nor did the last block show some error not in the answers list. What happened before was irrelevant for the execution of the last block.

I was also puzzled by the l_count variable, which was not used anywhere, but also dismissed it as irrelevant.

In my opinion, the quiz was very clear in asking what will be displayed on the screenafter executing THE LAST BLOCK ONLY, the one that contained the UPDATE statement,without involving in any way the output of the preceding statements executed.

I definitely do NOT want to tell everyone what the "teaching lesson" or objective of the quiz is before you take it.

First of all, that would, in many cases, more or less give away the answer.

Second, the question gives you all the information you need to answer the question. You don't need to know my objective to read code and figure out what it is going to do. The code (in the question and even in your applications) should speak for itself.

If it does not, then we have a problem with the quiz, and I issue a correction. That is not the case in this quiz.

What about my explanation for this quiz do you have a disagreement with? Isn't it the case that the second choice (unhandled exception) is what you see when you run the code? Why wouldn't that be a correct choice, regardless of what I now tell is "my objective"?

I think that this question is a bit on thin ice here. In the assumptions we have the following text "The only database objects and PL/SQL program elements ("things" that can be referenced in the quiz code) that exist are those defined in the context of the quiz or are available in a default installation of the Oracle instance." From this I assume that all code run before the question code has run successfully, with no errors.

If we are to find out if the setup code has failed, the question itself looses it's value. The fail part, as it is important, should be a part of the answer.

And if we go to the real world, a developer would have fixed the first problem before continuing to the second one.

Steve,Your explanation is justified because how you have worded the quiz, but I did not like this quiz. I feel that quiz is controversial and confusing because of how it is worded. Even if you had scored "none of the answer.." as correct, I would still find this quiz controversial.I chose nothing because in the past when you had blocks that would throw error in quiz, you had mentioned those specifically, but here this fact was hidden purposefully. This quiz looked like a tricky IQ test, rather than PL/SQL skill test. I think this question can be framed in various different ways to keep the difficulty level high and still test for this concept.

I have no problem with the scoring of the quiz even though I’m not happy with my performance. I foolishly assumed that the "skip_unsupported" option had been intended but was inadvertently left off. The question was completely fair (and definitely worthy of an advanced difficulty rating). An important lesson from this quiz: unless it is explicitly stated that the setup portion completes without raising an exception, do not assume such.

I gave feedback in the quiz itself but I'll repeat here since there is a thread about it. I missed the question because I also made the assumption that the "setup" portion of the quiz was trustworthy. So I only answered the "question" portion of the quiz.

I don't like that style of quiz as I don't find it to be helpful as a learning experience (the error is trivially obvious) nor is it a knowledge test of error logging, but rather it is a test of reading.

However, I don't feel a rescore is warranted though because all information needed "was" in the question. While I did not enjoy this quiz, the fault is entirely my own for overlooking the errors in the setup.

It has been suggested that the failure to create the error logging table should be been mentioned in the quiz. Doing so would have made this an intermediate level quiz at best. The real test here was to understand the implications of the table structure on attempted error logging. Some may argue that such is not strictly a PL/SQL issue; however, as has been expressed before, PL/SQL programs run in an Oracle database environment and knowledge of that environment is a prerequisite for effective PL/SQL programming.

There is always a tiny piece of "competition philosophy" hidden behind our comments on the blogs.

When we think of a learning experience proper, in the most straightforward sense of the term,then, unfortunately for us as competitors, most of the 100% additions to our personal knowledge treasure come from the mistakes that we make while answering the quizzes.

Specifically, when a quiz asks about something that is not even mentioned in the documentationand of which you might very easily and very legitimately be unaware, even if you have read the Oracle books entirely, then you are really learning something new and usually paying the price for it.A good example is the 12 October quiz about the release of the locks, which surprisingly enough,has "passed silently", without anybody raising any issue.

On the other hand, this quiz about error logging, which was very clear and did not contain any undocumented features, nor hidden tricks, has raised several reactions.Contrary to others, I don't think that this quiz was a test of reading, but rather one of real knowledge.Once you saw the CLOB column in the table and the DML error logging involved,if you indeed possessed the knowledge about the error logging data type restrictions,you could really answer it without problems.

Most of the quizzes, this one included, are an excellent means of verifying and fixing your knowledge, which in some cases may represent what is called a "book-knowledge" only or at least mostly.

It is hard to suppose that somebody has encountered effectively each and every situation about which he/she does posses theoretical knowledge acquired through reading, and reading a lot ...Practical experience, though offering sometimes strange situations for which you even cannot find a very precise and ultimate explanation, usually has some "repetitiveness" in its character, which does not leave too much place to experience each and every piece of knowledge.But, I dare say, once you (unfortunately) make a mistake in a PL/SQL Challenge quiz,you will always remember that one and will not probably make that mistake again,and, what else can this be called other than REAL LEARNING ?

Knowledge is endless, and probably everybody still has so very many things to learn, but, however, the satisfaction of being correct on a quiz is one of the best rewards that you can expect, it is your every day's recognition of your knowledge and value, something which most of the time you do not receive from the every day real life.

Looking from this angle, I think that all in all the PL/SQL Challenge does an excellent jobin keeping our "personal knowledge base" always eager :) :)

The assumptions contain absolutely nothing about a mandatory success rate for statements leading up to the actual question. Actually, when more complex code is needed ahead of a question, we usually put in a statement like: You can assume this code will compile successfully, and that should be considered a freebee to help players from spending too much time off topic.

If there are no hints, the lead-in is just as much part of the quiz as is the question that ultimately relates to the choices. Especially, do not count on single line statements being accompanied by a "this works" comment.

Many quizzes have statements to create and populate a table or create a helper function. The fact that this happens a lot may put players a tiny bit off guard, thinking... Hey, this stuff is trivial and it always works! Well, wake-up call, just because it looks easy, don't count on it! We see this at work and we should be aware of it when taking a quiz.

When I looked at the answers of the quiz, I was also a little bit irritated. But then I realised, this is just what happens in real life. Once I got a script from a supplier to run and it was more then 800K big for, what I assumed, was a tiny update, so I decided to inspect the code. The first command was a TRUNCATE (bye bye rollback). The lesson, never take anything for granted.

Regarding "You've never run a batch setup script that had errors, but you didn't notice because you assumed it all worked?" - you should have enabled spooling so that you can check the spool-file for errors (never trust scripts without examine the output!).

I personally answered wrong, but only because I thought the default value for the skip_unsupported parameter was true (thats one of the procedures where I always add names of the formal parameters and also always explicitly specify the skip_unsupported parameter).