31 March 2011

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.

7 comments:

  1. Unfair quiz! Those of us who use overloading and know the right way to use it, would never remember that Oracle fails to give a compile error when it is used INCORRECTLY. Another one of your trick questions, which is totally irrelavent to the real world use of good PL/SQL programmers. As a long time "PL/SQL Challenger", these are the quizzes after which I sware I'll never do another one!

    If something fails to give a compile error but is an incorrect use of the feature, it is a BUG in Oracle and should be viewed as such. These errors in the PL/SQL language should not logged in our memory as something that is required to keep in mind. If it is a wrong use of the feature, it is WRONG. Simply because it "works in certain cases" (in this case, with a named paramter list) and does not give the needed compilation error, it is not a situation that needs to be remembered.

    I use a mix of positional and named parameter lists, all for very good reasons. I use overloading frequently. I would NEVER code a function or procedure that would work ONLY if the calling program used a named parameter list, and I know would fail if a positional parameter list were used.

    Perhaps I've misunderstood the point of the PL/SQL Challenge all along. So your goal is to have us professional PL/SQL programmers learn and know all the quirky, incorrect things that PL/SQL does, instead of learning new, perhaps obscure, features of PL/SQL?

    As you said in your own post, the Oracle documentation states clearly that the answer you marked as "correct" - simply because Oracle INCORRECTLY does not report the error - is flat out wrong. Since you obviously knew that, your quiz question was clearly designed to trip us up.

    So, what's your point??

    ReplyDelete
  2. I feel I also need to mention that JUST LAST WEEK I gave a "Basics of PL/SQL" presentation to a few hundred people. I included some slides about overloading, so the Oracle requirements and constraints for utilizing the overloading feature were clearly in my mind. And I NEVER would have told the audience that "well, you can do code it this way, Oracle won't give you a compilation error, and it will certainly work sometimes, but not all the time". I think you get my point.

    Clearly a trick question and answer, and designed to trip up those of us who know what we're talking about (at least sometimes!).

    ReplyDelete
  3. Hey Dan, settle down. I generally agree with your point but let's give these guys a break. They come up with a quiz EVERY DAY and you're not paying a dime to play. Your complaint seems awfully angry given that this is a FREE service with FREE prizes. Chill out and try to be a little more constructive instead of "swaring to never take the quiz again".

    ReplyDelete
  4. Thanks, Anonymous, but let's face it: it doesn't matter how much time we spend on this...either it is useful or it isn't.

    Dan, I am very glad to hear you are teaching PL/SQL to hundreds of developers - I am sure they will benefit greatly from your knowledge.

    Regarding this quiz, I don't think it's a matter of the quiz being tricky. It's more a matter of PL/SQL being (at times) a tricky language.

    One of the objectives of the PL/SQL Challenge is to transfer the knowledge I've acquired about PL/SQL as I explored its nooks and crannies. Surely, you would agree that you now know something about PL/SQL that you did not previously?

    You wrote: "If something fails to give a compile error but is an incorrect use of the feature, it is a BUG in Oracle and should be viewed as such. These errors in the PL/SQL language should not logged in our memory as something that is required to keep in mind. If it is a wrong use of the feature, it is WRONG. Simply because it "works in certain cases" (in this case, with a named parameter list) and does not give the needed compilation error, it is not a situation that needs to be remembered."

    Hey, you don't have to remember it if you don't want to - but surely it is helpful to know that this can happen! My point was not that you ever should write overloading like this, only that you can run into some rather quirky scenarios if you happen to overload excessively or carelessly.

    You can consider this a bug in Oracle or not; the main thing is to be aware that this can happen. This awareness could help you either avoid a bug or identify the cause of the bug more quickly.

    To conclude, this quiz was not intended to trick and in fact (to my mind) offers an important piece of practical information for PL/SQL developers.

    I sure hope, Dan, that you continue to play the PL/SQL Challenge - and share your viewpoints with us.

    Regards, SF

    ReplyDelete
  5. The question was about a successful compilation, not about a proper usage. So answers are scored correctly - and fairly. And we do need to remember such peculiarities - at least to be able to explain someone why his successfully compiled program cannot be called.

    Regards,
    Oleksandr

    ReplyDelete
  6. I totally agree with Steven. Knowing these things would definitely help and may be we can report them to Oracle.

    I am not sure if it can be considered as a bug but knowing this would certainly help us to avoid bugs in our own code.

    I look forward to more of these "tricky" quizzes in future :) And once again thanks to Steven and his team for giving us this opportunity to explore PL/SQL.

    Best regards,
    Ramesh

    ReplyDelete
  7. It is like the TO_CHAR problem of a month. When I was checking the answer, because I could not believe it is such a simple quiz, I found out that I had given the wrong answer. It took at least 15 minutes before I found out what was going wrong. And I consider it a bug in Oracle. But one you cannot change and worse, it is very important to know that this can happen.

    ReplyDelete