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

First quiz on user-defined aggregate functions draws comments (2124)

On Friday, 24 March, the PL/SQL Challenge offered its first quiz, by Randy Gettman, on user-defined aggregate functions. This topic, certainly an advanced one, presents its own challenge: how to offer a relatively brief quiz on a most complicated topic.

It drew these two comments:

morkCallingOracle(comeIn.oracle).... I mean what has "the world" become? Seriously, most non SF-quizzes need multiple times the number of code lines SF ones need. Then if you managed to work your way through the code - in fact this time I gave up - they often seem far fetched or even trivial. Ofc OO is not my thing at all. I think re-adjusting my expertise to Intermediate is appropriate here. How important is OO to the quiz' audience or strategically to PL/SQL? Keep it up people! :)

Thanks for yesterday's quiz. It might have been more fun if there was an answer using only built-ins "select exp(sum(ln(partnum))) from plch_parts;" (even though it will work only when partnum is always > 0) ..and even though the purpose of quiz was to teach custom aggregate. :)

I will respond to the first comment and leave the other for players to discuss.

This was a very code-heavy quiz (gee, maybe I really shouldn't do this sort of thing on a Friday!). In fact, when Randy first submitted it, my initial reaction was "This is just too much." Then I took a closer look and realized that so far as I could tell, this is just about the minimal amount of code you could possibly write to create such a function. So my choice became: never do a quiz on this topic (involving code) or give it a try. I am glad we "tried."

The first commenter might well be correct that on average player-contributed quizzes have more code than my own. The most likely explanation for that, however, is that quizzes provided by players often deal with more complex and edgy features, on which I am not an expert. If I were, I expect that my quizzes on such topics would have a similar volume of code.

Finally, in terms of "far fetched" or "trivial" or "how important is OO" - remember: we offer a new, fresh, delightful quiz every single weekday. That's a whole lot of quizzes, folks. And if we only covered "core" or "fundamental" features, well....we'd end up with lots of repetition or lots of trivial, boring quizzes. Plus, you wouldn't stretch and expand your knowledge of PL/SQL-related technologies.

The first player says "in fact this time I gave up" - and I can understand that. Sometimes a quiz will serve primarily as an introduction for you to a new area of technology. So you have to accept the fact that you are (temporarily) ignorant and delight in the opportunity to learn something new.

But go ahead and take the quiz anyway! "Give up," but still submit an answer! Unless it's one of those "only once choice correct:", you are bound to get some credit for the time spent realizing you have no idea what is going on in the quiz ( :-) )! And, by the way, we strive to avoid setting a quiz to "one choice correct" when it is advanced.

Cheers, Steven

Buggy behavior on some 11.1 versions with INTEGER variables (2123)

The 23 March quiz explored nuanced differences between a PL/SQL INTEGER type and a SQL INTEGER type.

In particular, inserts into a table of an integer with more than 38 digits does not cause an error to be raised, as one might have thought.

Several players wrote, however, that when they tried to run the verification code, they did, in fact, get the"ORA-01438: value larger than specified precision allowed for this column" error.

After further testing, it looks like this script works (and thus demonstrates the point of the quiz properly) on database instances of versions 10.2 and 11.2. On some sub-releases of 11.1, however, such as 11.1.0.6, the script fails with the error noted above. On 11.1.0.7, this bug is apparently fixed and the script runs without error.

The author of this quiz, "_Nikotin", also found that "it's possible to reproduce other bug-like behaviour with INTEGER (on 10.2, 11.1, 11.2):

SQL> drop table t;

Table dropped.

SQL> create table t (a integer);

Table created.

SQL> insert into t values (1E125);

1 row created.

SQL> alter table t modify a number(38);

Table altered.

SQL> insert into t select a from t;

1 row created.

SQL> insert into t values (1E125);
insert into t values (1E125)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> select a from t;

         A
----------
1.000E+125
1.000E+125

SQL> select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST_USER"."T"
   (  "A" NUMBER(38,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"