28 August 2010

Problems with 27 August quiz - undocumented features, ambiguous wording (1365)

The 27 August quiz tested your knowledge of the ALL_PROCEDURES data dictionary view, which gives you information about stored program units on which you have EXECUTE authority. There were a few problems with this quiz: 1. The ability to check the type of the program unit (through a column named OBJECT_TYPE) was missing in early releases of 10.2, including the Oracle XE version (10.2.0.1) and was undocumented in 10.2 documentation. Undocumented features do not concern me too much - you should run some code or connect to your instance to see what is really going on or is available, but it is a bit much to expect you to be aware of sub-release patch numbers in which a feature is added. 2. One of the multiple choices scored as correct was "This view allows you to determine whether a schema-level program unit is a function or procedure." Yet this is not really the case. A schema-level program unit could be a function, procedure, trigger, package or type. 3. The OVERLOAD column referenced in one of the answers was only added in Oracle11g, though there is a way to figure out whether or not a subprogram was overloaded through a query like the following (thanks, Sebastian, for providing this!):
select object_name, procedure_name
from all_procedures
where object_name = 'YOUR_PACKAGE_NAME_HERE'
having count(*) > 1
group by object_name, procedure_name;
Between the documentation/release issues of #1 and the semantical problems of #2, I have decided to give everyone a score of "correct" for this choice: This view allows you to determine whether a schema-level program unit is a function or procedure. I will then re-rank for the week and, finally, choose the winners of the week. Sebastian Kolski and Jeff Kemp both receive a prize of an O'Reilly ebook of their choice for pointing out these issues. I will also change the text of the OVERLOAD answer to add the query for 10.2 databases, and tighten up the language of the choice on which I am rescoring. Cheers, SF

3 comments:

  1. Hi,

    as may be seen from the SQL*Plus output below OVERLOAD column exists in 10.2 (at least 10.2.0.4):
    ================================================
    SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 30 09:29:33 2010

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    Name Null? Type
    ----------------- -------- ---------------------
    OWNER VARCHAR2(30)
    OBJECT_NAME VARCHAR2(30)
    PROCEDURE_NAME VARCHAR2(30)
    OBJECT_ID NUMBER
    SUBPROGRAM_ID NUMBER
    OVERLOAD VARCHAR2(40)
    OBJECT_TYPE VARCHAR2(19)
    AGGREGATE VARCHAR2(3)
    PIPELINED VARCHAR2(3)
    IMPLTYPEOWNER VARCHAR2(30)
    IMPLTYPENAME VARCHAR2(30)
    PARALLEL VARCHAR2(3)
    INTERFACE VARCHAR2(3)
    DETERMINISTIC VARCHAR2(3)
    AUTHID VARCHAR2(12)
    SQL>
    ================================================
    So it was not not "only added in Oracle11g".

    Then regarding wording - "This view allows you to determine whether a schema-level program unit is a function or procedure." does not imply that the program unit may be ONLY a function or procedure.
    Otherwise one more wording should be treated as ambiguous, namely "The value of the object_name column is not NULL and that of the procedure_name column is NULL if the program unit is a schema-level procedure or function".

    ReplyDelete
  2. Thanks, al0. It sure would be nice if Oracle would document new features added incrementally, so there was a clear record of what was added when.

    I will update my quiz to make sure it is accurate.

    What do you think is ambiguous about "The value of the object_name column is not NULL and that of the procedure_name column is NULL if the program unit is a schema-level procedure or function"?

    SF

    ReplyDelete
  3. Ah...and one other thing: the ALL_PROCEDURES select I show in my original post should have an "OWNER =" clause in it, as should any query against an "ALL*" view. Otherwise you might get "hits" for objects in other schemas.

    This is not an issue in the quizzes, since one of the assumptions states that the only DB objects present are those defined in the quiz itself, but that is NOT an assumption you can rely on in the real world.

    ReplyDelete