Ambiguous wording in 1 April quiz? (2145)

The 1 April quiz offered this question:

Over the years, many development teams have created large scripts in anonymous blocks to run in the background and perform all sorts of complex data management tasks. Which of the choices describe a reason to move as much code as possible from the anonymous blocks into procedures and functions stored in the database (either schema-level or within packages)? 

Several players raised a concern about the scoring of this choice as correct:

The business logic in the scripts is more likely to be reused.

Here is a comment that reflects the concerns:

You marked the "The business logic in the scripts is more likely to be reused." as a correct answer, but I have great doubts about its wording. From my point of few this wording should be interpreted as "business logic that stay directly in the scripts (as oppose to be moved to stored objects) has more chances to be reused". Definitely incorrect statement. 

I would agree with this objection, if I had to decide on the correctness of this choice separately from the question. All by itself, "The business logic in the scripts is more likely to be reused." can be interpreted to mean either that the logic is more likely to be reused when left in the anonymous block OR when it is moved to a stored program unit.

But when playing the quiz, you do not consider these choices out of the context of the question, and the question clearly asks "Which of the choices describe a reason to move as much code as possible from the anonymous blocks into procedures and functions...?"

So I believe that the wording of the question removes any possible ambiguity - no change in scoring is required.

Another player wrote: "Can an anonymous block be optimized to the same degree as a stored procedure? The first thing that came to mind seeing that option was that an anonymous block cannot be natively compiled."

My response: I am actually not certain about what Oracle will do with anonymous scripts when native compilation is enabled, but I feel that when you are working with a language whose compiler offers automatic optimization (true of Oracle PL/SQL since 10.1), a statement like "The compiler will optimize the code in the stored program units, but it cannot do the same for an anonymous block." would most obviously and clearly relate to that optimization process and not a special feature of the Oracle Database like native compilation.

Again, no change in scoring.

Your thoughts?
SF

Comments and questions about date format masks (2144)

The 31 March quiz tested your knowledge of the use of the "Month" format mask for TO_CHAR. We received the following comments and questions:

1. "ELSE 'Not Short and Not Long *'||v1||'*'||v2||'*' . answer: Not Short and Not Long *September*October * Why is the length 2*9 positions and not 10?"

2. "Hi, I'm referring to the daily quiz for 31 March 2011. This quiz was very interesting, testing the players' various areas of PL/SQL functions and data types. Unfortunately, in the post-quiz comment, the author only mentioned how the data format model affects the return value, but forget to talk about the importance of data types and implicit conversions.

"If v1 and v2 were declared as CHAR, then blank-padded comparison semantics would have been used, and the output would be 'Long and Sour'.

"On the comparison semantics: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements002.htm#sthref305

3. I have read the explanation but still don't know why it is like that. Am sure there will be a hot discussion on this issue. I personally feel it's a bug. Waiting for the discussion to start on the blog.

I have invited Jeff, the author of the quiz, to reply; we will also undoubtedly update the explanation after this conversation is finished.

Another quarter ends!

Dear PL/SQL Challenge Players:

Another quarter has ended, a new quarter has begun.

And you know what that means:

1. I will be announcing the participants in the 2011-Q1 playoff championship soon (likely next week).

2. Everyone starts with a "clean slate" for Q2. If you weren't happy with your ranking last quarter, you have the opportunity to play diligently and carefully and keep your ranking up high.

3. If you have been playing non-competitively and would like to switch, now (the first week of every quarter) is the time to switch back to competitive play.

I will send out a newsletter with detailed information about activity of all players in the quarter. But here's an early glimpse:

Total number of unique players: 1,722
Total answers submitted: 21,604
Time playing quizzes: 73 days 15 hrs 44 mins
Total countries playing: 86

More to come, thanks again to everyone for playing - I hope you've learned a lot. Please spread the word to other developers to join us!

Warm regards,
Steven Feuerstein

Conflict between documentation on overloading - and reality (2143)

The 30 March quiz tested your knowledge of overloading (also known as "static polymorphism"), the ability to define more than one subprogram with the same name in the declaration section of a PL/SQL block (or in a package). It showed this code:

CREATE OR REPLACE PACKAGE plch_ovld_pkg
IS
   PROCEDURE my_program (d_in IN DATE, n_in IN NUMBER);
   
   [OTHER_SUBPROGRAMS]
END plch_ovld_pkg;
/ 

and asked:

Which of the choices can I put in place of [OTHER_SUBPROGRAMS] that will allow me to compile the package specification without any errors?

Several players raised the same objection:

"Hello, in http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#i12352 chapter "Restrictions on Overloading" it says that "You cannot overload two subprograms if their formal parameters differ only in name ...", but the Quiz for 30 March 2011 did accept that as correct. Did I miss something?"

In other words, they felt that the following choice should have been marked as incorrect:

PROCEDURE my_program (date_in IN DATE, n_in IN NUMBER);

Sadly, this is one of those cases in which the Oracle documentation is incorrect.

It is certainly possible to compile a package specification without errors even if two subprograms differ only by the names of one or more of their formal parameters. The verification code shows this.

When, however, you overload in this manner (differences only in formal parameter names), you will either (a) have to use named notation to distinguish between the overloadings or (b) you may not be able to actually call the overloaded subprogram.

In the case of the plch_ovld_pkg code, it turns out that there is no way to call successfully the original subprogram:

PROCEDURE my_program (d_in IN DATE, n_in IN NUMBER);

I can call the overloading that uses the date_in argument, but when I try to call the my_program procedure above, every form that I use raises the "PLS-00307: too many declarations of 'MY_PROGRAM' match this call" error - even when I use named notation.

The reason is that if I use named notation to distinguish this from the "date_in" version, as in:

BEGIN
   plch_ovld_pkg.my_program (d_in => SYSDATE, n_in => 1);   
   plch_ovld_pkg.my_program (date_in => SYSDATE, n_in => 1);
END;
/

the PL/SQL compiler cannot distinguish the first invocation from this other overloading:

PROCEDURE my_program (n_in IN NUMBER, d_in IN DATE);

and the block fails to execute.

I will add more of this explanation to the answer for this quiz. But there is no doubt about it: Oracle will let you compile the package specification without error. You just won't be able to call all of the subprograms you defined.

Different mutating table behavior when using SYSDBA account (2142)

The 29 March quiz focused on the fact that a mutating table error can be raised not only when a trigger is executed, but also when a function is called from within an SQL statement.

A player noticed that when you run the verification code for this quiz from a SYSDBA account, no error is raised.

I lack the time to do more experimentation; perhaps a player can see if this change in behavior extends to mutating table errors in triggers - and also uncover an explanation for this behavior.

Objections to scoring on NULL arguments quiz (2141)

The 28 March quiz asked:

"Most character functions return a NULL if the values of any arguments passed to the functions is NULL. Which of the functions listed do not necessarily return NULL if an incoming argument is NULL?"

The first email I got on this quiz said: "While I understand the answers provided, I did have a little trouble interpreting the question - although I'm not sure how I might have worded it differently as yet. It will be interesting to see how much feedback you receive on this one."

And usually if any single player raises a concern about wording, well, there's more to come. And there was:

"I have an objection for yesterday's quiz. It is about CONCAT. You asked about necessity of returning NULL "if an incoming argument is NULL". So, when one of arguments is null but other argument is not NULL - that is also suitable case. Isn't it? But there is not necessity of returning NULL in this case. Which means that this function "do not necessarily return NULL if an incoming argument is NULL". May be I have not clearly enough understood this question, but this is my point."

"I did not mark the answer regarding NULLIF as a valid answer, since I found that: NULLIF(NULL, 12) would return an ORA-00932 error because expr1 can not be the literal NULL."

"What about select substr('abcde', 2) from dual;? In this case it returns 'bcde', a not-null result. I thougt, the not-mentioned 3rd parameter counts as a NULL."

"When you call nullIF function with null as first input parameter error is raised. So i think that NULLIF is not a correct answer. I have attached the script which shows that. The error is 'PLS-00619: the first operand in the NULLIF expression must not be NULL'."

And now my responses:

First, regarding the semantics of the question: I suppose I could have worded this quiz differently. I believed and still believe that the text of the question is equivalent to the following:

"Is it possible to call the function so that at least one of the arguments is NULL and it returns a non-NULL value?"

That is, if one of the arguments is NULL, that does not mean that the return value of the function would have to ("necessarily") be NULL. So all you have to do is identify a scenario in which you can call the function with NULL and have it return a non-NULL value for it to be a correct choice.

I can see why there might have been confusion with the phrasing (it's sorta, kinda like a double negative because NULL is, well, not very positive). I do not, however, feel that my use of the language here is ambiguous. On to the specifics raised....

CONCAT: if one of the aarguments to CONCAT is NULL and the other is not NULL, then the value returns is not NULL.

NULLIF: Just because you can find one scenario in which a NULL argument causes an error to be raised, does not mean this choice is incorrect. You can also identify a scenario in which you do  pass a NULL value and it returns a non-NULL value.

SUBSTR: you don't have to pass a value for the third parameter of SUBSTR - but that's because Oracle provides a default value for that parameter. So the question then is: what is that default value? In this case, it is not NULL, as you can see from the header of the program in the STANDARD package:

function SUBSTR(STR1 VARCHAR2 CHARACTER SET ANY_CS,
                POS PLS_INTEGER,
                LEN PLS_INTEGER := 2147483647)
      return VARCHAR2 CHARACTER SET STR1%CHARSET;

So I have no plans to change my scoring on this quiz. Your thoughts?

Regards, SF