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