04 October 2011

Different parameters for UTL_FILE.FCOPY in 10g and 11g? (7982)

The 3 October quiz focused on ways to use UTL_FILE package to copy the contents of one file to another file.
Two players wrote with a concern about a possible conflict between the stated minimum version of this quiz (10.2)  and the parameter list in the calls to UTL_FILE.FCOPY. Here's one of the comments:

There is an issue with today's quiz on fcopy. The minimum version stated is 10.2, however the formal parameters referred to for utl_file.fcopy are those in 11 - which have changed since. The first four formal parameters have been renamed.

Both of the players indicated that they had ignored what they saw as a problem, and answered it "based on what I think the intention was."

Good move, players! Because in fact there is no difference between the parameter lists of FCOPY in 10.2 and 11.2. The only difference is in the documentation.

In the 10.2 doc, FCOPY is described as:
   location   IN VARCHAR2,
   filename   IN VARCHAR2,
   dest_dir   IN VARCHAR2,
   dest_file  IN VARCHAR2,
   start_line IN PLS_INTEGER DEFAULT 1,
While in the 11.2 doc, FCOPY is documented as having the following parameter list:
   src_location    IN VARCHAR2,
   src_filename    IN VARCHAR2,
   dest_location   IN VARCHAR2,
   dest_filename   IN VARCHAR2,
   start_line      IN BINARY_INTEGER DEFAULT 1,
But if you actually look at the UTL_FILE package specification in a 10.2 installation (which you can find in the ORACLE_HOME/Rdbms/Admin/utlfile.sql file), you will see that in fact even "way" back in 10.2, the parameter names matched those in 11.2.

Which just proves once again that you are always better off actually checking the code compiled into the database than relying on the (any) documentation!


  1. So, "everyone lies", as it dr.Hause had stated out. And we can't rely on documentation, but every time check spelling in db? According to documentation your statement will cause an exception in 10.2 if we'll try to execute it.

  2. When I sent the feedback I thought it rang some big bells, but I didn't have a chance to look into it further. Can you remember if this specific issue has been talked about before on PL/SQL challenge?

    Either way, this sounds like another instance where the challenge has (re)located issues in the documentation, and hopefully the Oracle team can get it rectified.

    I really like the Oracle documentation, and on a side note I hope the Apex documentation can mature to a similar stature. I'm glad these sort of errors are few and far between.


  3. Hi folks,

    that was the reason I didn't marked it as correct. I know and used the FCopy() routine but always used it without parameter-names. So I took a quick look at the docs for the parameter names and was fooled by the bug in the docs.

    Even inside of the package in the comments directly above the procedure the parameter names are different to the names of the specification (which is still the same in a database).

    But how should I check the code at home where I don't have an Oracle installation (I had some days off from work).

    Greetings, Niels

  4. You keep assuming everyone has access to all the Oracle files.

    In your run-up conversation to this blog you ask the rhetorical question "Did we make a mistake?" You answer "No" and I would agree, but I think you made a bad choice in defaulting to a 10.2 minimum and then referencing the 11.2 documentation. Might as well say the minimum is 11.2.

  5. When the code and documentation don't match, sometimes it is a code bug, sometimes it is a documentation bug. I think it could be risky to make a Challenge-wide declaration that, where the database observed behavior and documented behavior disagree, the observed behavior will always be taken as correct.

  6. As you may have noticed, all documentation references are for 11g and there are several reasons for that:

    1. Oracle rarely remove features from the database, and 10g documentation is the basis for 11g documentation.

    2. 10g, while still supported, will enjoy no more upgrades. Neither to the code nor to the documentation.

    3. Although the minimum version (starting with 10g) is shown for all quizzes, 11g has been out now for more than 4 years and a fair number of developers/players are likely to use that version, at least in development.

    4. Switching between documentation sets is cumbersome and generally not necessary, simply because the 11g documentation has everything we need to know about 10g as well.

    5. Oracle do an excellent job at evolving the database (SQL and PL/SQL) and they do so, almost to perfection, without comprimising our existing applications. As a result of that, new versions add new features and all the old stuff usually continues to work.

    Bottom line... 11g documentation is what Oracle currently maintain, and it is the best source of up-to-date information, even for 10g.

    The issue with utl_file.fcopy is a rare blooper! The package specification documents argument names that are implemented differently in the procedure right below. I know that I have looked at that package several times and never noticed the error. Maybe because I have never applied named arguments for calls to utl_file. In the early years of 10g, it would have taken no more than a simple documentation bug report. Why didn't it happen? Well, probably noone felt responsible. Lesson learned... If you see something wrong, report it!

    Something about trick questions and choices:
    We, the reviewers, spend a fair amount of time testing, correcting and amending all the quizzes. It does happen that we overlook something important, mostly at the outer perimeter of values or behavior, and that is when players step in to teach us a lesson ;-)

    However, quizzes are not released with tricks related to something as deep as argument names. Names of built-in's, sure, but once we are at that level, choices will evolve around correct use of the details and not how they are spelled.


  7. Hello All,

    Mike put it excellently, chapeau :) :)

    Oracle documentation is indeed one of the most valuable sources of information that I have ever encountered, even if as versions go ahead,
    they have a tendency "to shorten" the presentation somewhat ...
    As an example, we can think of some of the Oracle supplied packages, among those with general purpose usage like DBMS_SQL, DBMS_LOCK, a.s.o. whose functionality was presented extensively in the Application Developer's Guides in older versions, while in the last ones some much fewer details can only be found in the Built-ins Reference guide...

    So, one finds itself working more and more with
    the Reference Guides as a "primary source" of information, while previously it was just a secondary one, probably not used at all by many developers, and, also, sometimes looking back into the documentation of a previous version for details that are not contained any more in the later versions.

    Many Oracle authors, though, emphasize in their books that those books are meant "to teach you HOW TO USE the Oracle features", while the Oracle Documentation just explains you how the features work.
    In that sense, they are a mandatory addition
    to the excellent Oracle Documentation set.

    Regarding the "tricky quizzes", I still cannot forget that one with the semicolon omitted on
    purpose at the end of a loop, to make the next word (statement) "become" a label ...

    Since then, I learned that quiz text should be read "character by character" ... and yes, that takes sometimes a very long time ...

    Thanks & Best Regards,

  8. Iudith, I remember that quiz with the missing semicolon and I agree that it was borderline.

    I now have more than a year's worth of reviewer experience and among all of the many lessons learned, two really stick out:

    1. Is the question contained? Are there ways of legitimately deriving something that isn't covered by the choices? Not always easy.

    2. Apart from wife saying that I should be doing other things, I have time to chew on the questions and to try out choices. The issue here is that a reality check is needed. No matter how much time reviewers spend on a quiz, the final solution must be digestable (by players) in a reasonable amount of time.

    Based on what I have learned, I will not let another "semicolon" quiz get through. Syntax quizzes are fine, but they have to teach something of real value. The "semicolon" quiz was valid, but did exploit a scenario that is unlikely to become a reality issue.

    To any player reading this; You do not have to spend time on making sure that every comma, semicolon, bracket or the like are properly lined up (unless told to do so). The PLSQL Challenge is not a spellchecker, it is here to help everybody learn, move forward, and hopefully to have some fun as well.