18 August 2011

ALL_PROCEDURES Evolves Over Versions (5980)

The 17 August quiz tested your knowledge of the ALL_PROCEDURES view. Several players objected to the scoring of the following choice as correct:

The program type in the view contains the type of the schema-level unit, not the type of the subprogram in the package.

Objections centered on two issues:

1. There is no "program type" in the view. There is only an OBJECT_TYPE column (and that, only in Oracle Database 11; more on that in point 2). As players wrote: "As far as I was concerned, there was no "program type" in the view therefore incorrect. More so given that it's not only functions, procedures and packages, but types and triggers. It's object type. Petty perhaps? That was how I read it." and "It makes it hard when the question makes up its own terminology for columns in the data dictionary views. I've always called it "object type" because that's the column name, consistent with many other data dictionary views."

2. The OBJECT_TYPE column was only added in Oracle Database 11, yet this quiz stated that 10.2 was the minimum version for which the quiz was valid.

Yes, this is certainly true as you can see from the SQL*Plus session below:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> desc all_procedures

Name                                      Null?    Type
----------------------------------------- -------- ------------
OWNER                                     NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
PROCEDURE_NAME                                     VARCHAR2(30)
AGGREGATE                                          VARCHAR2(3)
PIPELINED                                          VARCHAR2(3)
IMPLTYPEOWNER                                      VARCHAR2(30)
IMPLTYPENAME                                       VARCHAR2(30)
PARALLEL                                           VARCHAR2(3)
INTERFACE                                          VARCHAR2(3)
DETERMINISTIC                                      VARCHAR2(3)
AUTHID                                             VARCHAR2(12)
A correction will be needed.

Other players took issue with scoring the following choice as correct:

You can use this view to determine which of your subprograms are declared as deterministic.

If you simply check the documentation on ALL_PROCEDURES, then for some 10.2 versions of that doc, you will not see a mention of the DETERMINISTIC column. That column is, however, present in all releases of 10.2.

As one player noted, "I would like to solve quizzes where answers is possible to find in Oracle documentation. For instance for this quiz Oracle 10.2 documentation about ALL_PROCEDURES is incomplete."

I can certainly understand why a player might like us to do this, but the PL/SQL Challenge quizzes will not be constrained to the limitations of the Oracle documentation....that would make them of much less use to PL/SQL developers. We have to live in and program in the "real world", not the world presented in the documentation!

So to conclude:
  • I will change the minimum version of this quiz to Oracle Database 11g.
  • I will clean up the choice regarding "program type" to be more explicit about the column.
  • All players will receive credit for a correct selection for the "program type" choice.
Did I miss anything? :-)

5 comments:

  1. Ah, yes, I did miss something else. Another player wrote with the following observation:

    "The answer about using the all_procedures to determine which package routines are functions and which are procedures is confusing. I chose that to be false, because it can't be used reliably. However it can be used sometimes, procedures can't be declared deterministic, so if deterministic is YES then the routine must be a function. Same with pipelined or aggregate. So, my guess is you'll get players answering both ways based on either argument."

    Very interesting! Yes, certainly, for some entries in ALL_PROCEDURES you could deduce that it must be a function and could not be a procedure. But you cannot determine the subprogram type for all rows in the view.

    ReplyDelete
  2. Hello All,
    Though the issue of determining whether a packaged subprogram is a procedure or a function has already supplied us much fun and lots of creative action and learning since we play the PL/SQL Challenge, maybe time has indeed arrived to have this information explicitly included in the *_PROCEDURES views.

    I am almost sure that this will come in some future version, maybe as a PROCEDURE_TYPE column, that will accompany the PROCEDURE_NAME.
    It's strange enough that this was not done yet.

    Thanks & Best Regards,
    Iudith

    ReplyDelete
  3. Syed Ariful Bari19 August, 2011 06:11

    Excusme All,
    I am objecting about the titled issue, because in oracle db 10g version (10.2.0.3.0)include the column OBJECT_TYPE in the view ALL_PROCEDURES.
    Please take a look--

    SQL> select * from v$version;

    BANNER
    ----------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
    PL/SQL Release 10.2.0.3.0 - Production
    CORE 10.2.0.3.0 Production
    TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production

    SQL> desc all_procedures
    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)

    I agreed with the earlier versions argument but within 10g we get the output as above. So, we do not need to go for oracle 11g right now for this
    quiz.

    Thanks All

    Syed Ariful Bari
    A quiz perticipant

    ReplyDelete
  4. Thanks for pointing that out, Syed. The problem with not specifying 11g is that a person might be using 10.2.0.1 (the free download) and the column is not present in that release. We don't specify sub-release numbers for our assumptions, so we need to "move it up" to 11.

    ReplyDelete
  5. We have already learned how to find out if a program unit is a procedure or a function in the quiz from 2011-01-12: if a record exists where "POSITION = 0" in the all_arguments-view then it is a function else it is a procedure.
    Greetings, Niels

    ReplyDelete