PL/SQL Challenge upgrades to APEX 4.1

We upgraded the website to APEX 4.1 over the weekend. Overall, it went smoothly (thanks, John Scott of Apex Evangelists!). We had to upgrade some plug-ins, but that was easy enough.

Two issues have caught our attention, though:

1. It seems as though processing on the website has gotten noticeably slower in a few spots (I click on the Play Now button and wait 5-10 seconds to move to the quiz page (don't worry - this time is not recorded as part of your answer time!). Does anyone else see this happening?

2. URLs with checksums generated by apex_util.prepare_url no longer seem to work; that is, even though the URLs include a checksum, you see this error:

Session state protection violation: This may be caused by manual alteration of a URL containing a checksum or by using a link with an incorrect or missing checksum. 

The behavior on Firefox seems to be inconsistent. On Internet Explorer, though, the URLs are always failing and we just noticed that IE seems to be changingthe URL. That is, I paste in this URL into Firefox and it works fine:

http://plsqlchallenge.com/pls/apex/f?p=10000:PG_PQ_DRILLDOWN:442439591002222::NO:651:P651_COMP_EVENT_ID,P651_QUIZ_ID:5207,5987&cs=1F74C48CFF65FF02A47F87CB1CF821CBD

I then  paste it into IE8 and press enter, IE8 changes the url to:

http://www.plsqlchallenge.com/pls/apex/f?p=10000:PG_PQ_DRILLDOWN:3106820171411144::NO:651:P651_COMP_EVENT_ID,P651_QUIZ_ID:5207,5987%26cs%3D1F74C48CFF65FF02A47F87CB1CF821CBD

Notice that & is changed to %26. And then I see the checksum error.


Do you have any experience with this?

Cheers, Steven Feuerstein

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