One, Two, Many Implicit Conversions (5986)

A player brought to our attention some rather strange code in the 25 August quiz on TRUNC.

We asked you to use this block to determine your answer:

DECLARE
   multiplier   INTEGER := /*VALUE*/;
BEGIN
   FOR indx IN 1 .. 5
   LOOP
      DBMS_OUTPUT.put_line (
            'Trunc by '
         || NVL (TO_CHAR (multiplier * indx), 'NULL')
         || ' = '
         || TO_NUMBER (TRUNC (123456.123456, multiplier * indx)));
   END LOOP;
END;

And this is what he wrote:

"I just hope your construct is meant to illustrate "bad practice"? You have this line: "|| TO_NUMBER (TRUNC (123456.123456, multiplier * indx))". You are doing TO_NUMBER on a number which will implicitly convert the result of TRUNC to a string, which TO_NUMBER converts back. Then the result of TO_NUMBER is a number which you are concatenating to your output string, so that must also be an implicit conversion? Have I read it correctly or am I just not awake yet ? I guess it might be meant as a distraction or a way to force ORA-06502? Anyway - you may have your reasons, I just wanted to let you know in case it happened to be a typo and you meant TO_CHAR instead of TO_NUMBER..."

Well, I must admit: that was a typo. And a lucky typo....it did not affect the outcome of the quiz, because Oracle ending up "doing the right thing" for us through implicit conversions.

So I am going to change the quiz to use TO_CHAR. Thanks for pointing this out, Kim!

SF

APEX Support in Toad

Quest is exploring whether to and how best to add support for Oracle Application Express in Toad for Oracle.

More and more Oracle technologists are building websites in APEX technology. It is moving rapidly from the sidelines of departmental applications to the mainstream of robust, public and private websites implementing mission critical applications involving many users. For example, the PL/SQL Challenge website is built on APEX.

Hundreds of thousands of these same technologists also spend lots of their time in Toad for Oracle, building out the backend elements (tables, packages, etc.). Quest Software believes that Toad for Oracle should be able to help APEX developers understand, analyze, maintain and even enhance their applications from within Toad.

We also, realize, however, that there is an enormous range and depth of experience with APEX "out there." So rather than just come up with a list of features we think would be helpful to APEX developers, we'd like to hear from you, the APEX developer and also development DBAs supporting APEX developers, about how you think Toad for Oracle can best support your work with APEX.

You can do so by responding to this blog post. You can also email me directly at steven.feuerstein@quest.com. I will be compiling requests and requirements for consideration by the Toad for Oracle Product Manager and development team.

Many thanks in advance,
Steven Feuerstein
PL/SQL Evangelist, Quest Software

Daily Quiz Activity for 15 August 2011 - 19 August 2011

In the past week (15 August - 19 August), 790 Oracle technologists were busy submitting 2,376 answers to quizzes and exploring the library of past quizzes. Here are the PL/SQL quizzes played in that period:

15 August 2011: When you want to declare an associative array with an integer index, the datatype of the INDEX BY clause must be BINARY_INTEGER or any of its subtypes. You should, as a rule, always use PLS_INTEGER unless you have some specialized requirement.
              528 Players ♦ Avg. Correct: 64% ♦ Avg. Time: 221 seconds ♦ Rating: 4 stars

16 August 2011: Make sure that you are both aware of all possible built-in functions and that you use them in a way that optimizes both performance and maintainability.
              634 Players ♦ Avg. Correct: 73% ♦ Avg. Time: 444 seconds ♦ Rating: 4 stars

17 August 2011: ALL_PROCEDURES gives you information about the subprograms (procedures and functions, both schema-level and within packages) on which you have EXECUTE authority.
              633 Players ♦ Avg. Correct: 80% ♦ Avg. Time: 203 seconds ♦ Rating: 4 stars

18 August 2011: Use SAVEPOINT to define a point in your session's transaction to which you can rollback. This feature allows you to only partially rollback changes in your session.
              624 Players ♦ Avg. Correct: 70% ♦ Avg. Time: 273 seconds ♦ Rating: 4 stars

19 August 2011: You can compare a collection to NULL, but keep in mind that associative arrays will always evaluate to NOT NULL - even if they do not contain any data. Nested tables and varrays are NULL until initialized.
              591 Players ♦ Avg. Correct: 68% ♦ Avg. Time: 213 seconds ♦ Rating: 4 stars

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

Wrong Scoring for Stateful Package Quiz? (5193)

The 11 August quiz explored the concept of "state" in a package and asked you to identify ways to avoid the raising of the ORA-04068 error.

Two players wrote with objections to how the answers were scored. Here are their comments:

I guess answer 7626 was supposed to be correct, but the offered solution will not work. The new package body should reference the variable declared in plch_pkg_constant: CREATE OR REPLACE PACKAGE BODY plch_pkg IS FUNCTION language_vendor RETURN VARCHAR2 IS BEGIN RETURN plch_pkg_constant.g_language; -- <== END language_vendor; END plch_pkg; So "Move the declaration of the variable to another package" is correct, but "as shown below" is not. 

My reading of the choices is that none of them will give you the response of the "newly changed variable", however my feeling that the "Move the variable to the package" is the correct choice because, I am thinking, that that will allow you to repeatedly call the function even though the response will always be "Oracle Corporation" and not what you set the variable to. 

My response: I can see why these players were caught up on this issue, but I believe it results from a mis-reading of the quiz question, which ends with:

"Which of the choices describe a way to change the code in plch_pkg so that a user connected to the SCOTT schema can call the hr.plch_pkg.language_vendor function repeatedly, even if the default value of the g_language variable is changed in the HR schema?"

Notice that I say "call the hr.plch_pkg.language_vendor function repeatedly". The g_language variable (which gives "state" to the package, leading to the ORA-04068 error) is not used by the language_vendor function. Changing the value of this variable does not affect this function in any way. So I don't think this statement is true: "The new package body should reference the variable declared in plch_pkg_constant."

Of course, in the "real world", it is unlikely that you can move a variable from one package to another without having a ripple effect through other subprograms and program units. But I don't think that is an issue here.

Your thoughts?