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? :-)

Finding Holes in Algorithms - "Number in String" (5979)

The 16 August quiz stated the following:

I need to write a function named plch_num_instr that returns the number of times a substring occurs within a string. Here is the header of the function:

FUNCTION plch_num_instr (
   string_in IN VARCHAR2, substring_in IN VARCHAR2)

We then asked you:

"Which of the choices implement plch_num_instr so that the following block displays these five lines after execution..."

One player wrote after taking this quiz:

"Fine quiz but I think that none of the solutions would correctly implement your requirement (although I found the three correct answers for the quiz). Just try "plch_num_instr( 'Steieimer Meieieier', 'eiei')". The correct answer is 3 but all of your solutions just return 2 (haven't tried it before, but I have thought that the RegExp_Count() should return the correct value). The error lies in the implementation of the answer with id 7628 where you add the length of the substring to the actual starting position instead of adding just 1. This is no request for a scoring adjustment just a little bit to think about."  He also offered a corrected implementation, which you will find at the end of this posting.

And another player wrote with a similar concern: "In the given function calls, it give us correct answers, but there are cases when this implementation returns incorrect answer. For example: plch_num_instr ('123123123123', '123123'). Call to this implementation will return 2, but substring '123123' occurs 3 times within a string."

And finally as one player so succinctly expressed it: "Just out of interest, given this specification what should the following block display? BEGIN DBMS_OUTPUT.put_line (plch_num_instr ('steeeeeeven', 'ee')); END;"

All players are correct; the choices did not contain an implementation that handled all cases (nor did we claim that they would). I will add the implementation you see below to the answer, along with the points noted above. But no change will be made in scoring.

CREATE OR REPLACE
FUNCTION InStrCount (pString  IN VARCHAR2,
                     pSearch  IN VARCHAR2)
                    RETURN BINARY_INTEGER
/*====================================================================
    Returns the number of occurrences of a search-string in a string
  --------------------------------------------------------------------
    pString:  String to search for the occurrences of "pSearch"
    pSearch:  String to search in "pString"
  --------------------------------------------------------------------
    Result:  number of occurrences of "pSearch" in "pString"
  --------------------------------------------------------------------
    History:  000-00  nhecker
  ====================================================================*/
IS

    Result  BINARY_INTEGER := 0;
    iPos    BINARY_INTEGER;
    iStart  BINARY_INTEGER := 1;

BEGIN
  LOOP
    iPos := InStr( pString, pSearch, iStart);
    EXIT WHEN (iPos = 0);

    Result := Result + 1;
    iStart := iPos + 1;
  END LOOP;
  RETURN (Result);
END InStrCount;
/

Last week in the Oracle PL/SQL Challenge Daily Quiz

In the past week (8 August - 12 August), 866 Oracle technologists were busy answering quizzes (over 2,600 answers wee submitted) and exploring the library of past quizzes. Here are the PL/SQL daily quizzes played in that period:

8 August 2011: In both SQL and PL/SQL, Oracle supports two "wildcard" characters:  "%" (percent sign) and "_" (underscore). You can use wildcard characters in LIKE conditions to look for matches on patterns in strings.
             633 Players ♦ Avg. Correct: 82% ♦ Avg. Time: 103 seconds ♦ Rating: 4 stars

9 August 2011: Prior to Oracle 11g, you could not directly reference fields of records in the binding array of FORALL. You would instead have to "break out" a collection of records into individual collections for each column to be updated or compared in the SQL statement; you could also update or insert entire records. In Oracle 11g, you can now reference those fields.
             685 Players ♦ Avg. Correct: 52% ♦ Avg. Time: 238 seconds ♦ Rating: 4 stars

10 August 2011: Make certain that whenever you are implementing any sort of increment or counter that the initial value is not NULL - and is not set to NULL along the way.
              672 Players ♦ Avg. Correct: 81% ♦ Avg. Time: 209 seconds ♦ Rating: 4 stars

11 August 2011: Packages that contain package-level data may cause ORA-04068 errors if the package body is recompiled. You can avoid this problem by declaring your package to be serially reusable, by moving the package-level data out of that package or, with 11.2 and higher, by using the Edition Based Redefinition feature.
              667 Players ♦ Avg. Correct: 57% ♦ Avg. Time: 309 seconds ♦ Rating: 4 stars