PL/SQL Challenge

Compiling Incomplete Program Units (9633)

2012-01-27T14:21:00.000Z

The 25 January quiz tested your knowledge of the minimal amount of code needed to "create" a PL/SQL procedure in the data dictionary, even if the resulting program unit is invalid.

The choices offered were:

CREATE PROCEDURE plch_test;
CREATE PROCEDURE plch_test AS;
CREATE PROCEDURE plch_test NULL;
CREATE PROCEDURE plch_test IS BEGIN NULL; END;

The first was scored as incorrect and the last three as correct.

Several players demonstrated ways to execute these statements in either Toad or SQL Developer that either (a) caused the first statement to create an object (invalid procedure) or (b) caused the third statement to not create an object in the database.

The author of this quiz, Jeff Kemp, did some research and found the following:

SQL*Plus: no problem - the first statement does not create any object, the latter 3 statements do create an object.

TOAD - I get varying results for option 3, depending on how I invoke the statement in the SQL editor.

Quiz Option 1: CREATE PROCEDURE PLCH_TEST;

1. Highlight statement, press F5

Result: "ORA-00911: invalid character" - no object created

2. Highlight statement, press F9

Result: "[Error] Syntax check (5: 27): Found: ';' Expecting: AS IS -or- ......" - no object created

3. Highlight statement, press Ctrl+Enter

Result: "[Error] Syntax check (5: 27): Found: ';' Expecting: AS IS -or- ......" - no object created

So I haven't been able to create an object using the first quiz option at all. By the way, each of the above tests were repeated after highlighting just the statement by itself, as well as highlighting the statement plus the following slash (/).

Quiz Option 3: CREATE PROCEDURE PLCH_TEST NULL;

1. Highlight statement, press F5

Result: object created with compilation error

2. Highlight statement, press F9

Result: "[Error] Syntax check (5: 27): Found: ';' Expecting: AS IS -or- ..." - no object created

3. Highlight statement, press Ctrl+Enter

Result: "[Error] Syntax check (5: 27): Found: ';' Expecting: AS IS -or- ..." - no object created

Looks like if you use the F5 option, the third CREATE PROCEDURE quiz option does work in Toad.

I, on the other hand, was able to get the first choice to create an object by pressing the green arrow button in the Editor menu in Toad. Go figure.

The assumptions for the PL/SQL quizzes state that you can use your choice of IDE (SQL*Plus, Toad, SQL Developer, etc.). For this quiz, that turned out to be a very tricky assumption, because the IDEs offer multiple ways to execute statements that handle the terminating character of the statements differently.

For some, the semi-colon is treated as part of the code. In others, it is treated as the terminator of the statement, to trigger execution.

As a result, I will be giving everyone credit for the 1st and 3rd choices. In addition, I have changed the question to specify that these statements execute in SQL*Plus and I have explicitly included the "/" to execute the buffer.

Q4 2011 Championship Playoff on 25 January

2012-01-23T19:19:00.000Z

The 7th quarterly championship playoff of the PL/SQL Challenge will take place on Wednesday, 25 January.

Forty-two players qualified to participate. In this playoff, players will have 30 minutes in which to take five quizzes. Scoring and ranking for the playoff follows the same formula used for the daily quiz.

While everyone cannot win, I wish all players the best as they demonstrate their expertise in the Oracle PL/SQL language!

Steven Feuerstein

Exploring DBMS_UTILITY.FORMAT_CALL_STACK (9628)

2012-01-19T15:51:00.001Z

The 18 January quiz tested your knowledge of the contents of strings returned by a call to DBMS_UTILITY.FORMAT_CALL_STACK, which (roughly speaking) answers the question: "How did I get here?" (with a nod to the Talking Heads, one of my all-time favorite bands)

Iudith Mentzel went exploring and came up with some interesting tidbits that I thought I would share.

First, she noted that "this quiz was very similar with the quiz from 21 April 2011. The only difference was that it mentioned a nested block for the procedure that is calling DBMS_UTILITY.FORMAT_CALL_STACK...by the way, in comparison with the previous one that was scored as Advanced, this one was even trickier and scored as Intermediate, which looks not very consistent."

You are absolutely right, Iudith. That was not very consistent. Among other things, once a reviewer pointed out that the bottom of the stack could under some circumstances not be an anonymous block, I should have changed it to Advanced.

With my spare moments, I am going back to older quizzes and cleaning them up (adding lesson summaries and verification code, for example, since these were not even stored with questions when the PL/SQL Challenge first started). In the process I am finding many quizzes whose levels of difficulty and other information should probably be adjusted. These changes would, however, also affect scoring. So at some point, we will tweak our backend so that "old" scores and ranking data are archived and will not be affected by changes in the questions.

Now on to more substantive thoughts from Iudith on the call stack function (her words in blue):

Checking the quiz scenario, I found some strange issues, here is my test case:

CREATE OR REPLACE PROCEDURE plch_proc1
IS
BEGIN
   BEGIN
       DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
   END;   
END;
/

-- Procedure created.

CREATE OR REPLACE PACKAGE plch_pkg1
IS
   PROCEDURE proc2;
END plch_pkg1;
/

-- Package created.

CREATE OR REPLACE PACKAGE BODY plch_pkg1
IS
   PROCEDURE proc2
   IS
   BEGIN
      plch_proc1;
   END;
END plch_pkg1;
/

-- Package body created.

CREATE OR REPLACE PROCEDURE plch_proc3
IS
BEGIN
   plch_pkg1.proc2;
END;
/

-- Procedure created.

-- testing
BEGIN
    plch_proc3;
END;
/

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000007FFD219EC48         5  procedure SCOTT.PLCH_PROC1
000007FFD1FF5810         6  package body SCOTT.PLCH_PKG1
000007FFD1F54060         4  procedure SCOTT.PLCH_PROC3
000007

For some reason, SQL*PLUS client (version 9i) did not display the output correctly; the "anonymous block" is NOT there ... though it should be.The above output looks like this after some editing, otherwise the lines were broken in the middle ...This was tested with an 11gR1 database (11.1.0.7.0) having UTF8 character set. The same SQL*PLUS client version, on a single-byte character set database was even stranger:

BEGIN
plch_proc3;
END;
/

ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1

Just "to make peace", I decided to put the value into a table and select afterwards to see it, so I rewrote plch_proc1 ... and now comes the interesting part:

CREATE TABLE plch_temp ( x VARCHAR2(4000) )
/

-- Table created.

--version 1 - insert the  function result directly into the table:

CREATE OR REPLACE PROCEDURE plch_proc1
IS
BEGIN
BEGIN
   INSERT INTO plch_temp VALUES (DBMS_UTILITY.format_call_stack);
END;   
END;
/

-- Procedure created.

BEGIN
plch_proc3;
END;
/

-- PL/SQL procedure successfully completed.

-- now we have "anonymous block" TWICE  !!!
SQL> SELECT * FROM plch_temp
2  /

----- PL/SQL Call Stack -----
object      line  object
handle    number  name
000007FFD1EE0C38         1  anonymous block
000007FFD219EC48         7  procedure SCOTT.PLCH_PROC1
000007FFD1FF5810         6  package body SCOTT.PLCH_PKG1
000007FFD1F54060         4  procedure SCOTT.PLCH_PROC3
000007FFD1E3EEB8         2  anonymous block

1 row selected.

ROLLBACK;
/

--version 2 - store the function result in a local variable before inserting:

CREATE OR REPLACE PROCEDURE plch_proc1
IS
x   VARCHAR2(4000);
BEGIN
BEGIN
-- DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);      
-- INSERT INTO plch_temp VALUES (DBMS_UTILITY.format_call_stack);
   x := DBMS_UTILITY.format_call_stack;
   INSERT INTO plch_temp VALUES (x);
END;   
END;
/

-- Procedure created.

BEGIN
plch_proc3;
END;
/

-- PL/SQL procedure successfully completed.

-- now it is finally as expected 
SQL> SELECT * FROM plch_temp
2  /

----- PL/SQL Call Stack -----
object      line  object
handle    number  name
000007FFD219EC48        10  procedure SCOTT.PLCH_PROC1
000007FFD1FF5810         6  package body SCOTT.PLCH_PKG1
000007FFD1F54060         4  procedure SCOTT.PLCH_PROC3
000007FFD1F3F920         2  anonymous block

1 row selected.

Just as a last remark, the command line SQLPLUS.exe of Oracle client version 11g displayed the output almost correctly with the original DBMS_OUTPUT.put_line, that is, "anonymous block" was displayed once, but with the lines still broken in the middle ...

But the above tests that insert into a table look however strange ...it would be interesting to hear if anybody does have any clue for this behavior .

Looks like calling the function DIRECTLY from a SQL statement does generate an additional "anonymous block" layer in the call stack ... which can even become misleading if one seriously considers using this function not just for "PL/SQL fun" ...

I know that you are very "fond of" the DBMS_UTILITY.FORMAT_CALL_STACK function, however, I don't think that using it "as is" is such a "big gift" made to the developers ... Maybe only by hiding it into another "text analyzing" package, like the sample one supplied .

Back to Steven....it is unlikely that very many of you are still running an Oracle9i client, so that is probably not much of an issue, though interesting to discover. Also good to know that calling the call stack function from within an SQL statement, inside PL/SQL, introduces another anonymous block to the stack. It makes sense to me that this "internal" anonymous block would disappear in that last example. The function is not being called from within the SQL statement any longer.

Thanks, once again, to Iudith for her fascinating explorations. Any comments from other players?

Cheers, SF

Serializable Transaction Impact Not Seen by Players (9622)

2012-01-11T18:12:00.003Z

The 10 January quiz tested your knowledge of serializable transactions and system change numbers. Several players ran the verification code and got "b = 0" for the second and fourth choices (9005 and 9007), which would have made them correct (they were marked as incorrect). Here's the report from one player:

I checked your verification code for the yesterday challenge about isolation level. On my database (Ora 10.2.0.4-64) the choices 9005 and 9007 are working fine and the output is "b = 0". If you run the verification code without the choices 9004 and 9006 it runs without error. Here is my testcase:

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 11 16:03:28 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table plch_test (a number, b number);
Table created.

SQL> begin
  insert into plch_test values (1, 0);
  insert into plch_test values (2, 0);
  commit;
end;
/ 

PL/SQL procedure successfully completed.

SQL> declare
  b number;
  2    3    procedure xxx
  4    is
  5      pragma autonomous_transaction;
  6    begin
  7      set transaction isolation level read committed;
  8       update plch_test
  9          set b = 1
 10        where a = 2;
 11        commit;
 12        select ORA_ROWSCN into b from plch_test
 13        where a = 1;
 14        dbms_output.put_line('ORA_ROWSCN_XXX = '||b);
 15    end;
 16  begin
 17    select ORA_ROWSCN into b from plch_test
 18    where a = 1;
 19    dbms_output.put_line('ORA_ROWSCN_start = '||b);
 20    set transaction isolation level serializable;
 21    dbms_lock.sleep(10);
 22    xxx;
 23    select ORA_ROWSCN into b from plch_test
 24    where a = 1 for update;
 25    dbms_output.put_line('ORA_ROWSCN_end = '||b);
 26  exception
 27    when others then
 28      dbms_output.put_line('Error');
 29  end;
 30  /
ORA_ROWSCN_start = 855358236
ORA_ROWSCN_XXX = 855358246
ORA_ROWSCN_end = 855358236

As you see, the SCN of the autonomous transaction is higher than the SCN from the select for update at the end. What is your explanation of this?

I have asked, _Nikotin, the author of the quiz to do some research and post his reply here.

Exploring Mutating Table Errors and FORALL (9619)

2012-01-10T21:54:00.000Z

The 5 January quiz tested players' knowledge of the fact that the mutating table error (ORA-04091) is raised differently for different ways of performing inserts and with a BEFORE row-level trigger.

Iudith Mentzel took the quiz as a starting point for some very interesting analysis, which I share here.

Hello Steven,

Following the quiz from January 5 about the mutating table error (ORA-04091), there was something in the explanation that arose my curiosity, so I tested it out and found something "half-strange".

Namely, it is the explanation of the correct choice [8740] that says the following:

"Oracle does not raise the mutating table error for the first row inserted. When it attempts to insert the row for the second element in the collection, the mutating table error is raised."

I performed the test below to prove that this is indeed the case and found the following:

CREATE TABLE plch_parts (
   partnum    NUMBER
 , partname   VARCHAR2 (30)
)
/

Table created.

CREATE OR REPLACE TRIGGER plch_parts_bir
   BEFORE INSERT
   ON plch_parts
   FOR EACH ROW
DECLARE
   cnt   NUMBER;
BEGIN
   -- just a control message
   DBMS_OUTPUT.put_line('BEFORE ROW trigger fired for '|| TO_CHAR(:new.partnum) );
   SELECT COUNT (*) INTO cnt FROM plch_parts;
END;
/

Trigger created.

/*
   Here we see that the mutating error happened indeed on the 2-nd row only,
   but it caused a rollback of the 1-st inserted row as well.
   This is usually NOT the case in a FORALL statement failure (for some other error),
   the results of the previous successful iterations are (generally) NOT rolled back
*/

DECLARE
   TYPE plch_parts_t IS TABLE OF plch_parts%ROWTYPE
                           INDEX BY PLS_INTEGER;
   t   plch_parts_t;
   cnt  NUMBER;
BEGIN
   t (1).partnum := 1;
   t (1).partname := 'A';
   t (2).partnum := 2;
   t (2).partname := 'B';

   FORALL i IN INDICES OF t
      INSERT INTO plch_parts
           VALUES t (i);
EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.put_line(SQLERRM);
        
          /* if the row inserted by the first iteration is not rolled back
             then here we should see "COUNT=1" */

          SELECT COUNT(*) INTO cnt FROM plch_parts;
          DBMS_OUTPUT.put_line('COUNT='||cnt);
END;
/

BEFORE ROW trigger fired for 1
BEFORE ROW trigger fired for 2

ORA-04091: table SCOTT.PLCH_PARTS is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.PLCH_PARTS_BIR", line 7
ORA-04088: error during execution of trigger 'SCOTT.PLCH_PARTS_BIR'

COUNT=0  =====>  this is strange  !!!

PL/SQL procedure successfully completed.

/*
   If we add a SAVE EXCEPTIONS , then the 1-st inserted row is NOT rolled back
   which is the expected behavior.

   However, the error displayed by SQLERRM is ORA-04091 and not the usual ORA-24381,
   which shows that in this case the entire FORALL is handled like a "single multirow INSERT",
   and not like an "array of (separate) INSERTS", as FORALL usually behaves.

   In spite of this, it does preserve the 1-st row inserted,
   so it only behaves "partially" as a FORALL ... SAVE EXCEPTIONS statement.
*/

DECLARE
   TYPE plch_parts_t IS TABLE OF plch_parts%ROWTYPE
                           INDEX BY PLS_INTEGER;

   t   plch_parts_t;
   cnt  NUMBER;
BEGIN
   t (1).partnum := 1;
   t (1).partname := 'A';
   t (2).partnum := 2;
   t (2).partname := 'B';

   FORALL i IN INDICES OF t SAVE EXCEPTIONS
      INSERT INTO plch_parts
           VALUES t (i);
EXCEPTION
     WHEN OTHERS THEN
          /* here we expect ORA-24381, and not ORA-04091,
             if the later is raised for the 2-nd row */
          DBMS_OUTPUT.put_line(SQLERRM);

          /* if the row inserted by the first iteration is not rolled back
             then here we should see "COUNT=1" */

          SELECT COUNT(*) INTO cnt FROM plch_parts;

          DBMS_OUTPUT.put_line('COUNT='||cnt);
          DBMS_OUTPUT.put_line('ERRORS='||SQL%BULK_EXCEPTIONS.COUNT);

          FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
          LOOP
             DBMS_OUTPUT.put_line(

'ERROR('||i||')='||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                 '( '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE||' )' );
          END LOOP;

END;
/

BEFORE ROW trigger fired for 1
BEFORE ROW trigger fired for 2

ORA-04091: table SCOTT.PLCH_PARTS is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.PLCH_PARTS_BIR", line 7
ORA-04088: error during execution of trigger 'SCOTT.PLCH_PARTS_BIR'

COUNT=1  =====> this is expected, but strange for a non-ORA-24381 error !


ERRORS=1
ERROR(1)=2( 4091 )

PL/SQL procedure successfully completed.

I checked the above in both 11.1.0.7.0 and 11.2.0.1.0 and the behavior is the same. I wonder whether there are other cases for which we can see something similar.

Fact Mining and the Weekly Logic Quiz (10983)

2012-01-07T16:58:00.001Z

In addition to the PL/SQL, SQL and APEX quizzes, the PL/SQL Challenge offers a weekly logic puzzle, modeled on the Mastermind game.

In the puzzle for the first week of 2012, the second choice (9084) stated:

"If 1 is in the solution, then 7 cannot be in solution."

This was scored as correct and offered a step-by-step logical "proof" of why this was so.

Unfortunately, as several players pointed out, my logic was flawed from the very start - because 1 could not be in the solution at all. As Jennifer explained so clearly:

"We know from the clues that 1 cannot be in the solution. Based on the 3rd clue that only one of the set '2143' is in the solution, we know that 5,6, and 7 must be in the solution. Therefore 1 cannot be in the solution because the 2nd clue states that only 2 of '1456' is in the solution. We know that 5 and 6 must be so 1 and 4 cannot be."

I will change that choice to incorrect; everyone's scores will be updated within the next 24 hours.

While I am unhappy with my error and the need to issue a correction, I am delighted that several players analyzed the puzzle closely enough to uncover the problem - and also because this process reinforces what is to me one of the most important lessons for programmers when playing Mastermind (and similar games):

Make sure that you fully "mine" all "clues" (results from tests of your code) for all possible information.

All too often we (I!) barely look at the results of a test, or the report of a bug, before we rush to the source code and scramble (flail around?) to apply a fix. In doing so, we (I) often overlook critical information, and this oversight can lead to lots of time lost and even the introduction of new bugs.

In this puzzle, I did not perform enough analysis to conclude that 1 could not be in the solution. By missing this fact, I then introduced a mistake into the quiz.

Lesson learned: before you start messing around with your code, make sure you have extracted all possible conclusions from tests and specifications. Then apply that knowledge in a systematic fashion.

Thanks to Jennifer, DKennedy, Bobby and Pavel for identifying this problem!

Steven

Hapy New Year and Participants in Q4 2011 Playoff

2012-01-07T16:11:00.001Z

I hope you enjoyed some relaxing and joyful times with family and friends...ah, but it is all over so fast. Fortunately, at least in Chicago, it doesn't seem like winter has actually arrived. Today, 5 January, the temperature is in the 50s (Fahrenheit; 10 degrees Celsius). I tore myself away from my beloved laptop to spend time on my bicycle. Ah, that was very nice...

Well, regardless of the temperature, it's time to get back to work - and fun: back to answering PL/SQL, SQL and APEX quizzes...and it's also time for the Q4 2011 championship playoff.

You will find below the list of players who have qualified, either through ranking, correctness or wildcard. The number in parentheses are the number of playoffs in which the player has previously participated. Which means 11 players have never participated in a playoff before. Watch out, seasoned players, for some hot competition.

Congratulations to everyone on the list and best of luck in the playoff!

We will soon announce the date of the playoff, after confirming availability with players.

Warm regards to all,
Steven Feuerstein

Players in the Q4 2011 Championship Playoff

Name	Rank	Qualification	Country
Alain Boulianne (1)	1	Top 25	French Republic
Stelios Vlasopoulos (1)	2	Top 25	Greece
Frank Schrader (5)	3	Top 25	Germany
Valentin Nikotin (3)**	4	Top 25	Russia
Syed Ariful Bari (1)	5	Top 25	Bangladesh
Ninoslav Čerkez (1)	6	Top 25	Croatia
Viacheslav Stepanov (3)	7	Top 25	Russia
Chris Saxon (2)	8	Top 25	United Kingdom
mentzel.iudith (4)	9	Top 25	Israel
kowido (3)	10	Top 25	No Country Set
Chad Lee (2)	11	Top 25	United States
monpara.sanjay (0)	12	Top 25	India
Mike Pargeter (4)	13	Top 25	United Kingdom
Jeff Kemp (6)	14	Top 25	Australia
Janis Baiza (3)	15	Top 25	Latvia
Kevan Gelling (3)	16	Top 25	Isle of Man
Hrvoje Torbašinović (2)	17	Top 25	Croatia
Randy Gettman (4)	18	Top 25	United States
Siim Kask (4)	19	Top 25	Estonia
Dejan Topalovic (1)	20	Top 25	Austria
Jerry Bull (2)	21	Top 25	United States
Anna Onishchuk (3)	22	Top 25	Ireland
_tiki_4_ (0)	23	Top 25	Germany
james su (3)	24	Top 25	Canada
Niels Hecker (5)	25	Top 25	Germany
Andre van der Put (0)	26	Wildcard	Netherlands
John Hall (3)	27	Correctness	United States
Yuan Tschang (1)	29	Correctness	United States
Justin Michael Raj (0)	56	Wildcard	India
Nina (0)	58	Wildcard	Russia
Joaquin Gonzalez (3)	60	Correctness	Spain
Dalibor Kovač (3)	61	Correctness	Croatia
Frank Schmitt (0)	82	Wildcard	Germany
ZoltanKekes (1)	83	Correctness	United States
Gideon Bruggink (0)	103	Correctness	Netherlands
Vincent Malgrat (0)	108	Correctness	French Republic
sbramhe (2)	123	Wildcard	No Country Set
Dennis Klemme (5)	146	Wildcard	Germany
andrewc (0)	206	Correctness	New Zealand
dsinagl (0)	313	Correctness	No Country Set
Pedro Bravet (0)	436	Correctness	Spain

**Valentin Nikotin was incorrectly left off the initial list of participants because in Q1 2012, he changed to non-competitive play so that he could help the PL/SQL Challenge by writing lots of quizzes. Due to a bug in our algorithms, that left him out of the qualifying process for the playoff. So we have added him into the playoff, and also kept Andre van der Put in the playoff (he is now ranked 26th) as a Wildcard player.

What is a "Generic" Oracle Error Message? (9614)

2011-12-30T16:37:00.001Z

The 29 December quiz tested your knowledge of the differences between various ways of raising errors and communicating error messages to users. It was a word-based quiz, as opposed to one that is mostly code so - no surprise - a number of players raised objections to some of the phrasing and scoring.

Let's go through these objections and see how much we can learn about PL/SQL through the process. Player comments are in blue. My response is in purple.

Choice 8990: Both the PEI and RAE implementations allow you to set the error code to one that is not used by Oracle and is returned by a call to SQLCODE.

A player wrote

1. PEI allows code from -20000...-20999, but not only such codes;

2. Several codes from -20000...-20999 is used by Oracle, for example: "ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes"

3. Is it unambiguous to say that outside this range all codes are used by Oracle, for example, does "ORA-22567: Message 22567 not found; product=RDBMS; facility=ORA" mean that it's used by Oracle?

My response: you are absolutely right that Oracle does, in a few packages, use some of "our" error codes, such as ORA-20000 and ORA-20000. I've always felt that this was rude behavior on Oracle's part. We only get 1000 error codes with which to work; surely, you could leave all of those to us! So good point, but I don't think it makes this choice wrong in any way. With both those implementations, I can choose to set the error code to one that is not used by Oracle (such as -20704). The choice does not claim that it is impossible for me to choose a code that Oracle also uses.

As to which codes Oracle "uses" - no, I would say that at least for now, -22567 is not in use. But it is certainly the case that Oracle could at some point use these error codes - and we cannot.

Choice 8989: PEI and VE offer "generic" Oracle error messages, while RAE provides an application-specific error message.

Two players raised questions about this choice, and both circle back to the use of the word "generic". My intention behind the use of this word, combined with the "Oracle error message" phrase, is that these are the error messages returned by Oracle and are the same across all installations of Oracle.

I marked answer 8989 as "Incorrect" because PEI uses an application-specific exception - i.e. it's not a "generic" Oracle error. I didn't realize this answer was about the error *message text* in particular. Seems like this answer was a bit ambiguous.

and

I disagree that "RAISE VALUE_ERROR" raises a "generic" error. It raises the very specific error associated with ORA-06502.

and

When you use PRAGMA EXCEPTION_INIT to change the error code of a user-defined exception, then the error message returned by SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK is not generic, it is blank. So I’d suggest that while VE offers a "generic" Oracle error message and RAE provides an application-specific error message that PEI does neither.

My response: the text of the choice is clearly about messages. And my point in this choice is that with the EXCEPTION_INIT pragma, you can change the error code associated with a named exception, but you cannot change the error message. As for VALUE_ERROR resulting in a specific rather than generic error message, I could understand this objection if I wrote the choice as follows:

PEI and VE offer a single "generic" Oracle error message, while RAE provides an application-specific error message.

That is, if I said or implied there was just one "generic" message. But I use the plural form, so I feel it is clear that I am talking about the error messages returned by Oracle, which cannot be changed by the developer with EXCEPTION_INIT or RAISE.

So is a blank message "generic"? If you do not use EXCEPTION_INIT with a user-defined exception, then the error message is, well, generic: "User-defined error". When you assign a different error code to a user-defined exception, the error message is then blank. Gee, I don't know, that seems rather generic to me!

Your thoughts?

Thanks for a Great Year!

2011-12-28T20:20:00.000Z

As 2011 comes to a close, I would like to thank the thousands of players who have played the quizzes at the PL/SQL Challenge, especially the daily PL/SQL quiz, and who have also volunteered their time to write quizzes, edit and review quizzes, and provide many ideas for ways to improve the website.

At the PL/SQL Challenge website this year, 4,670 Oracle technologists from 104 countries spent over 32,000 hours submitting 213,077 answers to quizzes. These numbers reflect a very impressive commitment by all these players to improving their skill set in PL/SQL, SQL and APEX.

I am grateful beyond words to our reviewers, listed below with the number of questions they reviewed in parentheses:

Michael Brunstedt (270)

Ken Holmslykke (175)

Elic (172)

Darryl Hurley (129)

Kim Berg Hansen (25)

Patrick Barel (18)

Viji Thatai (6)

Munky (3)

The impact of our reviewers can be seen most clearly in the reduction in errors in our quizzes. In the eight months of play in 2010, we issued corrections for 31 quizzes. In all of 2011, with the addition of SQL and APEX quizzes, we only needed to issue corrections for 20 quizzes. And since January 2011, there have never been more than 2 corrections in a month. That's still too many - and I take full responsibility for all errors! - but it is certainly a big improvement. I expect to see the number decrease further in 2012.

Next, my thanks to the following players who found the time to submit quizzes that were then played in 2011, listed below with the number of questions authored in parentheses:

_Nikotin (8)

Kim Berg Hansen (8)

mentzel.iudith (8)

koko (6)

Scott Wesley (5)

Jeff Kemp (5)

Patrick Barel (5)

Gary Myers (4)

Tim Hall (4)

Christian Rokitta (3)

Ken Holmslykke (3)

Joaquin Gonzalez (2)

anil_jha (2)

Vinod Kumar (2)

Sergey Porokh (2)

Jan Leers (iAdvise.be) (2)

Christopher Beck (2)

Chris Saxon (2)

Keith Hollins (1)

poelger (1)

Radoslav Golian (1)

Ramesh Samane (1)

Marc Thompson (1)

Alexander Polivany (1)

David Codl (1)

Oleg Borodin (1)

Viacheslav Stepanov (1)

Sreeguruparan PA (1)

D.J. Alexander (1)

Darryl Hurley (1)

senthil prakash Muthu Irulappan (1)

Ralf Koelling (1)

Sohilkumar Bhavsar (1)

Neal Hawman (1)

David Alexander (1)

Randy Gettman (1)

Oleg Gorskin (1)

Joni Vandenberghe (1)

voltrik (1)

Sailaja Pasupuleti (1)

Dennis Klemme (1)

Tony Winn (1)

Finally, we have been working on some exciting new features for quite awhile now, and it looks like 2012 is the year in which they will appear, so please be on the lookout for announcements of big changes at the PL/SQL Challenge site soon.

Warmest holiday wishes to everyone!
Steven Feuerstein

Are the Quizzes Too Long (Wordy)?

2011-12-21T13:50:00.005Z

A player submitted this comment about the 20 December quiz:

The question of this quiz was too much long and contained a lot of unnecessary information. I used a lot of time to read and understand the question (279 seconds) while it was over the functionality of the simple built-in function LAST_DAY.

This happens regularly in daily quizzes. Lots of quiz players like me are not English native speakers. (My English is very poor). We need to spend much time reading the question to fully understand. Therefore, we are necessarily disadvantaged as compared to the English speaking participants.

Looking back at this quiz, I realize that I introduced a table and data in the table when it really wasn't necessary. That lengthened the quiz and could have been avoided.

So, definitely, this quiz could have been shortened and I am sure that many of the quizzes could be "stripped down" to the very basics.

I would love to hear what you think about this. I will also create a poll about this.

EXTEND and NOT NULL

2011-12-19T14:31:00.000Z

The 6 December quiz tested your knowledge of the effect of a NOT NULL constraint on a variable declaration, in this case through the declaration of a collection whose datatype is declared as NOT NULL.

The last choice (8773) used a nested table as follows:

DECLARE
   l_string   VARCHAR2 (10);

   TYPE nt IS TABLE OF VARCHAR2 (10) NOT NULL;

   l_nt       nt := nt ();
BEGIN
   l_nt.EXTEND;
   l_nt (1) := l_string;
END;

When Oracle attempts to assign l_string to an element in l_nt, a VALUE_ERROR exception is raised. So far, so good.

But a player noticed something odd here. Marcus checked the documentation on EXTEND and found this:

Note:

EXTEND(n,i) is the only form that you can use for a collection whose elements have the NOT NULL constraint.

Yet the call to EXTEND did not raise an exception. Either the documentation is wrong or there is a bug in EXTEND. If it's the latter, it's a bug that has gone unnoticed for many versions. I have logged a comment to the Oracle Documentation team about this.

Thanks, Marcus!

%TYPE and RESULT_CACHE - Correction needed? (9604)

2011-12-16T13:36:00.000Z

The 15 December quiz tested your knowledge of 11.2's ability to automatically detect when the contents of a table on which a result cache function relies has been changed (and then invalidate the cache).

Two players notified us that even though we scored the following choice as incorrect (which meant, in the context of this quiz, that a change to the table would not invalidate the cache):

CREATE OR REPLACE FUNCTION plch_func (n IN NUMBER)
   RETURN NUMBER
   RESULT_CACHE
IS
   l_n   plch_tab1.n%TYPE := n;
BEGIN
   DBMS_OUTPUT.put_line ('Running plch_func');
   RETURN l_n;
END;

The output from the verification does not support this conclusion.

So, first: the reason that this version of the function should not result in an invalidation of the cache highlights the distinct between dependencies between database objects and "relies on" for a result cache.

The plch_func function depends on the plch_tab1 in the "traditional" sense: since there is a reference to the table in the function through the %TYPE declaration, when this table changes, the function is marked as invalid and will have to be recompiled (note that the Oracle11g fine-grained dependency feature reduces the scenarios under which this invalidation will occur).

But that is a compile-time dependency, and very different from the "relies on" relationship that Oracle tracks automatically for purposes of ensuring clean data in a result cache. In other words, if the contents (but not the structure) of he plch_tab1 table changes, the compilation status of plch_func is not affected.

This is not the case with the result cache feature, precisely because this cache is all about delivering data (the contents of the table) to users.

So...that was the idea: if the only "mention" of plch_tab1 in plch_func is %TYPE, then that function does not rely on plch_tab1, and changes to that table will not force an invalidation and flush of its cache.

Having said that, when I ran the verification code, I noticed that the output did not match my expectations. The first time I ran it, I saw this:

%TYPE Reference
1
1

when I should have seen this:

%TYPE Reference
Running plch_func
1
1

That seemed very strange. It was as though the compilation of the new version of the function did not flush the cache. I then put an update statement before the CREATE OR REPLACE to force the cache to be invalidated. Then I saw the expected results. Then I didn't believe that could possibly be necessary, so I removed the update statement and now do see the expected results consistently:

%TYPE Reference
Running plch_func
1
1

The two players who wrote in said they saw these results:

%TYPE Reference
Running plch_func
1
Running plch_func
1

Very odd! I cannot reproduce that, for sure. I'd love to hear about the experiences of any players who have an 11.2 instance with which to work (11.1 still requires that you provide an explicit RELIES ON clause to specify the tables). I have also added that update statement to the verification code.

Let me know what you discover!

Thanks, Steven

The Pain of a Missing WHERE Clause Predicate

2011-12-03T14:15:00.000Z

I sent the following email out to 1243 players today:

Dear PL/SQL Challenge player,

One of things I like most about the PL/SQL Challenge is that it keeps me humble. I am reminded on an almost daily basis by one or more players that there is lots for me learn about PL/SQL and the wider Oracle technology stack.

And then there are the moments of deep, deep humility - such as when I discover a nasty bug that has corrupted data in the PL/SQL Challenge repository. Painful lessons, those.

Unfortunately, I write to you about precisely just such a moment. We discovered recently that a DELETE statement was missing a critical predicate in the WHERE clause (did a chill just run down your spine, as it did me when I realized what was happening?). As a result, if a player pressed the "Start Over" button while taking the Oracle Magazine quiz, all the choices made by players for this quiz were deleted.

This bug went undiscovered for quite a long time, so it is not possible to recover the data. Instead, we are going to "wipe the slate clean", extend the close date on these quizzes, and offer you the opportunity to take them again.

Look on the bright side: the second time around, you will likely answer faster and with higher accuracy!

Our deepest apologies for wasting even a few moments of your time. I hope you will continue to play the daily, weekly and monthly quizzes, thereby deepening your expertise in key Oracle technologies.

Trying to look on the bright side of things, this same problem would have occurred if anyone competing in the quarterly championship playoff had pressed that same "Start Over" button (it is only visible when the competition has multiple quizzes in it). That would have wreaked havoc - and required me to come up with five more playoff-worthy quizzes.

Still, I feel horrible that we would have made such a mistake and not noticed it testing; and that I would cause any player to have to re-do something they've done already.

I have extended the close date for the Sept/Oct quiz to the end of the year. I hope that you all will find the time to take the quiz before the end of the year.

Again, my apologies....

Steven Feuerstein

The Complex World of Indexing (10193)

2011-12-03T13:58:00.000Z

The SQL quiz for the week ending 2 December tested your knowledge of the circumstances under which Oracle would use an index to optimize a SQL statement.

Two players registered concerns about scoring the first choice as correct. This choice was:

SELECT last_name
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')

My intention was to isolate a small enough sampling of the total data set so as to invoke the index - and to also point out (through other choices) that if you use the column inside an expression, the index will not be used.

Here are the comment; I look forward to your thoughts.

Steven Feuerstein

1. I think, that the first choice for this quiz is ambiguous. The problem is, that the query selects about 3% of the rows. With the index in place, the database can choose either table full scan or index range scan followed by table access by rowid to run the query. Based on the assumptions, it's impossible to tell, which execution plan will be more efficient and hence whether the index improves query performance. In real world situation, we could expect, that the table is "almost" ordered by hire_date, which means that the clustering factor of the index would be low and the index access path would be more efficient than full table scan. However, there was no such assumption specified for this quiz. It would be better to include last_name in the index or select just a single month or a week instead of a year.

2. I have performed some research around the topic of this quiz, just to verify a little bit more deeper the case of choice number 1, which selects about 1/31 of the rows ( 1 year out of 31 years ) and it seemed to be the most obvious among all the choices. But query optimization is a huge topic, never ending and always dynamic, sometimes very frustrating and always exciting to research. I have performed some research around the topic of this quiz, just to verify a little bit more deeper the case of choice number 1, which selects about 1/31 of the rows ( 1 year out of 31 years ).

That is, though the query returns about 3% of the rows only, this is still not a complete guaranty that the index will be used in any case. It highly depends on the clustering factor of the index, or, in other words, it depends on how the rows were loaded into the table, in which degree they were loaded ordered by the HIRE_DATE column.

Below are two test cases, one in which the data is loaded with minimum clustering of the hire_date values (values generated in a circular fashion) and the other in which the values are highly clustered.

In both cases I used a direct-path insert for creating the test data faster.Then, for each case, I created the index and checked the execution plan:

a. without any statistics collected, which means that dynamic sampling will be used
b. with statistics collected using 20% sampling
c. with complete statistics collected.
Also, I use two slightly different queries:

1. One query identical with that of choice 1 in the quiz:

SELECT last_name
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/

2. One that selects the HIRE_DATE instead of the LAST_NAME

SELECT hire_date
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/

The conclusions are as follows:

1. When the rows are loaded cyclically by HIRE_DATE (not clustered, but dispersed), then the index is NOT used. Here dynamic sampling accurately estimates cardinality.

2. When the rows are loaded clustered by HIRE_DATE, then the index is always used, even for high cardinality estimated. Here dynamic sampling highly underestimates cardinality.

3. The index is always used when the query can be resolved entirely using the index only ( ex. selecting HIRE_DATE instead of LAST_NAME ).

See below the two test cases:

SET TIMING ON
SET LINESIZE 130
SET PAGESIZE 0


----------------------------------------------------------
CASE 1 -- data loaded in a circular fashion
----------------------------------------------------------
CREATE TABLE plch_employees
(
   employee_id     INTEGER
,  last_name       VARCHAR2 (100)
,  first_name      VARCHAR2 (100)
,  hire_date       DATE
,  salary          NUMBER
)
/

Table created.

Elapsed: 00:00:02.02

-- create a test case (10,000,000 rows) using smaller steps to avoid error
-- ORA-30009: Not enough memory for CONNECT BY operation

DECLARE
   K  PLS_INTEGER;
BEGIN
   FOR I IN 1 .. 1000
   LOOP
       K := (I-1)*10000;

       INSERT /*+APPEND */ INTO plch_employees
       SELECT K+ROWNUM, 
              'ABC '||(K+ROWNUM),
              'XYZ '||(K+ROWNUM),
               TO_DATE('01011980','DDMMYYYY') + MOD(K+ROWNUM, 31*365),
               10000 + MOD(K+ROWNUM, 20000)
       FROM DUAL
       CONNECT BY LEVEL <= 10000 ;

       COMMIT;
   END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:01:59.05


CREATE INDEX i_plch_employees
ON plch_employees (hire_date)
/

Index created.

Elapsed: 00:00:50.02

-- check execution plans without any statistics (will use dynamic sampling)

/* TEST1: selecting LAST_NAME */

EXPLAIN PLAN SET STATEMENT_ID='T1' FOR
SELECT last_name
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/
Explained.

Elapsed: 00:00:00.07


select plan_table_output 
  from table(dbms_xplan.display('plan_table','T1','serial'))
/

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   326K|    50M|  6883  (10)| 00:00:27 |
|*  1 |  TABLE ACCESS FULL| PLCH_EMPLOYEES |   326K|    50M|  6883  (10)| 00:00:27 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

19 rows selected.

Elapsed: 00:00:00.00

/* TEST2: selecting HIRE_DATE instead of LAST_NAME - index only access suffices */

EXPLAIN PLAN SET STATEMENT_ID='T2' FOR
SELECT hire_date
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/
Explained.

Elapsed: 00:00:00.00


select plan_table_output 
  from table(dbms_xplan.display('plan_table','T2','serial'))
/

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |   326K|  2866K|   869   (2)| 00:00:04 |
|*  1 |  INDEX RANGE SCAN| I_PLCH_EMPLOYEES |   326K|  2866K|   869   (2)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

19 rows selected.

Elapsed: 00:00:00.00

-- gather statistics with 20% sampling
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,'PLCH_EMPLOYEES',
                                   cascade     => TRUE,
                                   method_opt  => 'FOR ALL INDEXED COLUMNS',
                                   estimate_percent => 20);
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.00

/* TEST3: selecting LAST_NAME */

EXPLAIN PLAN SET STATEMENT_ID='T3' FOR
SELECT last_name
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/

Explained.

Elapsed: 00:00:01.04

select plan_table_output 
  from table(dbms_xplan.display('plan_table','T3','serial'))
/

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   315K|    12M|  6867  (10)| 00:00:26 |
|*  1 |  TABLE ACCESS FULL| PLCH_EMPLOYEES |   315K|    12M|  6867  (10)| 00:00:26 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

15 rows selected.

Elapsed: 00:00:00.03

/* TEST4: selecting HIRE_DATE instead of LAST_NAME - index only access suffices */

EXPLAIN PLAN SET STATEMENT_ID='T4' FOR
SELECT hire_date
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/

Explained.

Elapsed: 00:00:00.00

select plan_table_output 
  from table(dbms_xplan.display('plan_table','T4','serial'))
/

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |   315K|  2463K|   907   (2)| 00:00:04 |
|*  1 |  INDEX RANGE SCAN| I_PLCH_EMPLOYEES |   315K|  2463K|   907   (2)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

15 rows selected.

Elapsed: 00:00:00.00

-- gather full statistics  -  almost same results as with 20%  
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,'PLCH_EMPLOYEES',
                                   cascade     => TRUE,
                                   method_opt  => 'FOR ALL INDEXED COLUMNS',
                                   estimate_percent => 99);
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:02:52.03

/* TEST5: selecting LAST_NAME */

EXPLAIN PLAN SET STATEMENT_ID='T5' FOR
SELECT last_name
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/
Explained.

Elapsed: 00:00:00.00

select plan_table_output 
  from table(dbms_xplan.display('plan_table','T5','serial'))
/

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   315K|    12M|  6867  (10)| 00:00:26 |
|*  1 |  TABLE ACCESS FULL| PLCH_EMPLOYEES |   315K|    12M|  6867  (10)| 00:00:26 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

15 rows selected.

Elapsed: 00:00:00.03

/* TEST6: selecting HIRE_DATE instead of LAST_NAME - index only access suffices */

EXPLAIN PLAN SET STATEMENT_ID='T6' FOR
SELECT hire_date
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/
Explained.

Elapsed: 00:00:00.00

select plan_table_output 
  from table(dbms_xplan.display('plan_table','T6','serial'))
/

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |   315K|  2462K|   853   (2)| 00:00:04 |
|*  1 |  INDEX RANGE SCAN| I_PLCH_EMPLOYEES |   315K|  2462K|   853   (2)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

15 rows selected.

Elapsed: 00:00:00.00


DROP TABLE plch_employees
/

----------------------------------------------------------
CASE 2 -- data loaded clustered by HIRE_DATE
----------------------------------------------------------
CREATE TABLE plch_employees
(
   employee_id     INTEGER
,  last_name       VARCHAR2 (100)
,  first_name      VARCHAR2 (100)
,  hire_date       DATE
,  salary          NUMBER
)
/

Table created.

Elapsed: 00:00:02.02

DECLARE
   K  PLS_INTEGER;
   N  PLS_INTEGER := TRUNC(10000000 / (31*365));
BEGIN
   FOR I IN 1 .. 1000
   LOOP
       K := (I-1)*10000;

       INSERT /*+APPEND */ INTO plch_employees
       SELECT K+ROWNUM, 
              'ABC '||(K+ROWNUM),
              'XYZ '||(K+ROWNUM),
               TO_DATE('01011980','DDMMYYYY') + FLOOR( (K+ROWNUM)/ N ),
               10000 + MOD(K+ROWNUM, 20000)
       FROM DUAL
       CONNECT BY LEVEL <= 10000 ;

       COMMIT;
   END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:01:51.00

-- check correct (expected) distribution
SELECT COUNT(*)
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/

  COUNT(*)
----------
    322295

1 row selected.

Elapsed: 00:00:13.04

CREATE INDEX i_plch_employees ON plch_employees (hire_date)
/

Index created.

Elapsed: 00:00:47.01

/* TEST1: selecting LAST_NAME --

now index is used, though wrong cardinality estimation ! */

EXPLAIN PLAN SET STATEMENT_ID='T1' FOR
SELECT last_name
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/
Explained.

Elapsed: 00:00:00.00

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |  2500 |   393K|    26   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PLCH_EMPLOYEES   |  2500 |   393K|    26   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_PLCH_EMPLOYEES |  2500 |       |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

19 rows selected.

Elapsed: 00:00:00.04


/* TEST2: selecting HIRE_DATE instead of LAST_NAME - index only access suffices */

EXPLAIN PLAN SET STATEMENT_ID='T2' FOR
SELECT hire_date
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/

Explained.

Elapsed: 00:00:00.00

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |  2500 | 22500 |     9   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_PLCH_EMPLOYEES |  2500 | 22500 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

19 rows selected.

Elapsed: 00:00:00.01


-- gather statistics with 20% sampling
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,'PLCH_EMPLOYEES',
                                   cascade     => TRUE,
                                   method_opt  => 'FOR ALL INDEXED COLUMNS',
                                   estimate_percent => 20);
END;
/
PL/SQL procedure successfully completed.

Elapsed: 00:00:48.02


/* TEST3: selecting LAST_NAME - now with correct (much higher) cardinality, index still used ! */

EXPLAIN PLAN SET STATEMENT_ID='T3' FOR
SELECT last_name
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/
Explained.

Elapsed: 00:00:00.00

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   318K|    12M|  3160   (2)| 00:00:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PLCH_EMPLOYEES   |   318K|    12M|  3160   (2)| 00:00:12 |
|*  2 |   INDEX RANGE SCAN          | I_PLCH_EMPLOYEES |   318K|       |   861   (2)| 00:00:04 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

15 rows selected.

Elapsed: 00:00:00.00


/* TEST4: selecting HIRE_DATE instead of LAST_NAME - index only access suffices */

EXPLAIN PLAN SET STATEMENT_ID='T4' FOR
SELECT hire_date
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/
Explained.

Elapsed: 00:00:00.00

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |   318K|  2490K|   861   (2)| 00:00:04 |
|*  1 |  INDEX RANGE SCAN| I_PLCH_EMPLOYEES |   318K|  2490K|   861   (2)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

15 rows selected.

Elapsed: 00:00:00.00


-- gather full statistics  -  almost same results as with 20%  
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,'PLCH_EMPLOYEES',
                                   cascade     => TRUE,
                                   method_opt  => 'FOR ALL INDEXED COLUMNS',
                                   estimate_percent => 99);
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:02:42.05

/* TEST5: selecting LAST_NAME -

now with correct (much higher) cardinality, index still used ! */

EXPLAIN PLAN SET STATEMENT_ID='T5' FOR
SELECT last_name
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/
Explained.

Elapsed: 00:00:00.00

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   321K|    12M|  3035   (2)| 00:00:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PLCH_EMPLOYEES   |   321K|    12M|  3035   (2)| 00:00:12 |
|*  2 |   INDEX RANGE SCAN          | I_PLCH_EMPLOYEES |   321K|       |   868   (2)| 00:00:04 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

15 rows selected.

Elapsed: 00:00:00.00


/* TEST6: selecting HIRE_DATE instead of LAST_NAME - index only access suffices */

EXPLAIN PLAN SET STATEMENT_ID='T6' FOR
SELECT hire_date
  FROM plch_employees
 WHERE hire_date BETWEEN TO_DATE ('01-01-2010', 'MM-DD-YYYY')
                     AND TO_DATE ('12-31-2010', 'MM-DD-YYYY')
/
Explained.

Elapsed: 00:00:00.00

-------------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |   321K|  2511K|   868   (2)| 00:00:04 |
|*  1 |  INDEX RANGE SCAN| I_PLCH_EMPLOYEES |   321K|  2511K|   868   (2)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("HIRE_DATE">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE(' 2010-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

15 rows selected.

Elapsed: 00:00:00.00

Looking for a few good...SQL quizzes

2011-12-02T15:34:00.000Z

Friends,

As many of you know, we now offer a weekly SQL quiz, in addition to the daily PL/SQL quizzes.

I have a nice solid backlog of PL/SQL quiz submissions from players - and I must use these very gradually due to their impact on rankings (the author gets maximum score possible for their quiz).

We are in need, though, of SQL quizzes. I can and will write them, but my SQL expertise is not very expert. I'd love to feature the experience and creativity of players from all over the world.

Just click on Submit Quiz and have a go at it. I am happy to help you "polish up" the text, so don't worry about the fine details. Just make sure you provide working verification code, a nice variety of chocies, and a solid explanation from which I can work.

Many thanks in advance,
Steven Feuerstein

Late Report:: Results of Q3 2011 Championship Playoff

2011-12-02T15:23:00.000Z

[My apologies to players of the championship - I just discovered that I had neglected to publish this post way back in October.]

You will find below the results of the Q3 2011 championship playoff, played on 25 October. The number next to the player's name is the number of times that player has been in a playoff.

You can take a look at the quizzes that were in the competition through the Library page. These results will also be available through the Rankings page.

Congratulations to all players, of course, but let's give a special round of applause to our top three players:

1. Frank Schrader, who has played in an astonishing five playoffs (this is the 6th since we started the PL/SQL Challenge in April 2010) and wins a US$250 Amazon.com gift card.

2. Kevan Gelling, who has played in three playoffs (a fine accomplishment in and of itself) and wins a US$175 Amazon.com gift card.

3. Justin Cave, who has played in four playoffs (very impressive) and wins a US$100 Amazon.com gift card.

Rank	Name (Playoffs)	Country	Total Time	Total Score
1	Frank Schrader (5)	Germany	17 mins 08 secs	3052
2	Kevan Gelling (3)	Isle of Man	19 mins 57 secs	2651
3	Justin Cave (4)	United States	19 mins 59 secs	2635
4	Randy Gettman (4)	United States	17 mins 49 secs	2629
5	_Nikotin (3)	Russia	16 mins 28 secs	2566
6	mentzel.iudith (4)	Israel	19 mins 54 secs	2512
7	Jeff Kemp (6)	Australia	13 mins 25 secs	2342
8	Niels Hecker (5)	Germany	17 mins 09 secs	2267
9	Jerry Bull (2)	United States	19 mins 54 secs	2247
10	Janis Baiza (3)	Latvia	18 mins 45 secs	2235
11	Siim Kask (4)	Estonia	19 mins 27 secs	2201
12	james su (3)	Canada	16 mins 46 secs	2070
13	Anton Scheffer (2)	Netherlands	16 mins 04 secs	1974
14	Viacheslav Stepanov (3)	Russia	19 mins 32 secs	1954
15	Ludovic Szewczyk (2)	Belgium	19 mins 59 secs	1850
16	Joaquin Gonzalez (3)	Spain	16 mins 42 secs	1786
17	kowido (3)	No Country Set	19 mins 32 secs	1649
18	Ninoslav Čerkez (1)	Croatia	19 mins 22 secs	1638
19	Chad Lee (2)	United States	19 mins 56 secs	1596
20	Syed Ariful Bari (1)	Bangladesh	05 mins 59 secs	1550
21	Anna Onishchuk (3)	Ireland	17 mins 32 secs	1519
22	Dmitry Pushkashu (1)	Moldova	16 mins 24 secs	1462
23	Michal Cvan (4)	Slovakia	19 mins 41 secs	1426
24	Thierry Poels (2)	Belgium	18 mins 32 secs	1394
25	Rajesh Venkataramani (2)	India	19 mins 26 secs	1196
26	Dejan Topalovic (1)	Austria	14 mins 09 secs	1147
27	macabre (2)	Russia	19 mins 47 secs	1099
28	Alain Boulianne (1)	French Republic	15 mins 11 secs	1061
29	Spoon (2)	Singapore	19 mins 40 secs	957
30	dobloman (1)	Netherlands	16 mins 16 secs	700
31	John Hall (3)	United States	19 mins 41 secs	481

Nuances of Deterministic Functions in PL/SQL (9588)

2011-11-30T17:17:00.003Z

The 23 November quiz tested your knowledge of the little-known fact that as of Oracle Database 11g, the compiler will optimize the execution of deterministic functions in PL/SQL blocks. Previously, defining a function with the DETERMINISTIC clause led to optimizations when the function was called inside an SQL statement, but not in PL/SQL code itself.

_Nikotin, a devoted and high-ranking PL/SQL Challenge player, took the quiz as a launch point for an in-depth analysis of this optimization and came up with several interesting conclusions:

1. The optimization only occurs when the values passed as arguments to the function are compile-time constants, such as 1 or 'abc' or true or even TO_NUMBER('123') when the optimization level is set to at least 2. If you pass to one of the IN arguments an expression that cannot be determined at compile time to be a constant , no optimization occurs. Makes perfect sense, as this optimization occurs at compile time.

2.The optimization does not seem to occur outside of the context of a loop. In other words, calling the function N times with the same compile-time static arguments explicitly in your code results in the body of the function being executed N times.

3. Furthermore, if the loop contains non-deterministic code (that is, it is not simply calling deterministic functions with compile-time static constants), then the optimization also does not occur.

So at this point, it looks to _Nikotin and I that the applicability of this new optimization will be fairly narrow. Perhaps over time, the PL/SQL team will extend the circumstances under which the optimization will occur.

You will find below some details reinforcing the above points. In addition, I have attached a file to the list of resources for this quiz to demonstrate circumstances of optimization.

Cheers, Steven

DBMS_PROFILER Demonstrates No Optimization Outside of Loop (from _Nikotin)

def proc_name = PLCH_TEST_LOOPS
def func_name = PLCH_TEST_DETERM

create or replace function &func_name
(
i varchar2
) return varchar2
deterministic is
begin
return i;
end;
/

create or replace procedure &proc_name is
res varchar2(1);
begin
res := &func_name('a');
res := &func_name('a');
res := &func_name('a');
res := &func_name('a');
res := &func_name('a');
res := &func_name('a');
res := &func_name('a');
res := &func_name('a');
res := &func_name('a');
res := &func_name('a');
end;
/

var res number
exec :res := dbms_profiler.start_profiler('&proc_name');

exec plch_test_loops;
exec :res := dbms_profiler.stop_profiler;

col text format a60
set pagesize 100

select s.text, d.total_occur
from plsql_profiler_data d
   , user_source s
   , plsql_profiler_units u
where d.runid =
      (
        select max(runid) runid
        from plsql_profiler_runs
        where run_comment = '&proc_name'
      )
and d.runid = u.runid
and u.unit_name in ('&proc_name', '&func_name')
and d.unit_number = u.unit_number
and s.name = u.unit_name
and s.line = d.line#
order by u.unit_number, d.line#;

And the results:

TEXT                                        TOTAL_OCCUR
------------------------------------------- -----------
procedure PLCH_TEST_LOOPS is                                           0
res := PLCH_TEST_DETERM('a');              1
res := PLCH_TEST_DETERM('a');              1
res := PLCH_TEST_DETERM('a');              1
res := PLCH_TEST_DETERM('a');              1
res := PLCH_TEST_DETERM('a');            1
res := PLCH_TEST_DETERM('a');              1
res := PLCH_TEST_DETERM('a');              1
res := PLCH_TEST_DETERM('a');              1
res := PLCH_TEST_DETERM('a');              1
res := PLCH_TEST_DETERM('a');              1
end;                                         1
function PLCH_TEST_DETERM                    0
return                                    10
end;                                        10

For comparison with loops:

def proc_name = PLCH_TEST_LOOPS
def func_name = PLCH_TEST_DETERM

create or replace function &func_name
(
i varchar2

) return varchar2
deterministic is
begin
return i;
end;
/

create or replace procedure &proc_name is
res varchar2(1);
begin
for i in 1 .. 10 loop
    res := &func_name('a');
end loop;
end;
/

var res number
exec :res := dbms_profiler.start_profiler('&proc_name');

exec plch_test_loops;
exec :res := dbms_profiler.stop_profiler;

col text format a60
set pagesize 140

select s.text, d.total_occur
from plsql_profiler_data d
   , user_source s
   , plsql_profiler_units u
where d.runid =
      (
        select max(runid) runid
        from plsql_profiler_runs
        where run_comment = '&proc_name'
      )
and d.runid = u.runid
and u.unit_name in ('&proc_name', '&func_name')
and d.unit_number = u.unit_number
and s.name = u.unit_name
and s.line = d.line#
order by u.unit_number, d.line#;

And result is:

TEXT                                    TOTAL_OCCUR

--------------------------------------- -----------

procedure PLCH_TEST_LOOPS is              0

  for i in 1 .. 10 loop                  11

    res := PLCH_TEST_DETERM('a');        10

end;                                      1

function PLCH_TEST_DETERM                 0

  r                                       1

end;                                      1

Impact of Non-Deterministic Code Inside Loop

The following code uses inserts into a table (via an autonomous transaction) to demonstrate how often the body of the deterministic function is executed. You might think that this logging means the function is not deterministic and therefore will turn off the optimization, but that is not the case. The PL/SQL compiler does not validate that a function is trulydeterministic; it does not make a decision on whether or not to avoid execution of the function based on the code inside the function. All of that is decided at the time the code is compiled, based solely on the way the function is called - and, in turns out, the other code executed in the loop.

CREATE TABLE plch_log (created_on DATE)
/

CREATE OR REPLACE PACKAGE plch_getdata
IS
   PROCEDURE log_count (title_in IN VARCHAR2);

   FUNCTION vc (vc VARCHAR2)
      RETURN VARCHAR2
      DETERMINISTIC;
END;
/

CREATE OR REPLACE PACKAGE BODY plch_getdata
IS
   PROCEDURE log_count (title_in IN VARCHAR2)
   IS
      l_count   PLS_INTEGER;
   BEGIN
      SELECT COUNT (*) INTO l_count FROM plch_log;
      DBMS_OUTPUT.put_line (title_in || '=' || l_count);
   END;

   PROCEDURE log_call
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      INSERT INTO plch_log VALUES (SYSDATE);
      COMMIT;
   END;

   FUNCTION vc (vc VARCHAR2)
      RETURN VARCHAR2 DETERMINISTIC
   IS
   BEGIN
      log_call ();
      RETURN vc;
   END;
END;
/

DECLARE
   vc   VARCHAR2 (100);
BEGIN
   FOR i IN 1 .. 10
   LOOP
      vc := plch_getdata.vc ('abc');
   END LOOP;

   plch_getdata.log_count ('VC 10 iterations in loop');

   FOR i IN 1 .. 10
   LOOP
      vc := plch_getdata.vc ('abc');
      vc := 'abc' || TO_CHAR (SYSDATE);
   END LOOP;

   plch_getdata.log_count (

'VC 10 iterations with non-deterministic code in loop');
END;
/

VC 10 iterations in loop=1
VC 10 iterations with non-deterministic code in loop=11

DROP TABLE plch_log
/

DROP PACKAGE plch_getdata
/

Effect on Inlining Optimization (level 3) - from _Nikotin

Because Oracle can inline the local subroutines with plsql_optimize_level = 3, the "non-deterministic" part of the loop can became deterministic:

def proc_name  = PLCH_TEST_LOOPS

def func_name = PLCH_TEST_DETERM

create or replace  function &func_name

(

  i varchar2

) return varchar2 

   deterministic is

begin

  return i;

end;

/

alter session set plsql_optimize_level  = 2;

create or replace procedure &proc_name is

  res  varchar2(1);

  procedure inline_proc (i number) is

  begin

     null;

  end;

begin

  for i in 1 .. 10 loop

    res :=  &func_name('A');

    inline_proc(1);

  end  loop;

end;

/

var res number

exec :res :=  dbms_profiler.start_profiler('&proc_name');

exec plch_test_loops;

exec  :res := dbms_profiler.stop_profiler;

col text format a60

set pagesize  140

select s.text, d.total_occur

from plsql_profiler_data d

   ,  user_source s

   , plsql_profiler_units u

where d.runid = 

       (

        select max(runid) runid 

        from plsql_profiler_runs  

        where run_comment = '&proc_name'

      )

  and d.runid =  u.runid

  and d.unit_number = u.unit_number

  and s.name = u.unit_name

  and s.line = d.line#

order  by u.unit_number, d.line#;

And result:

TEXT                                              TOTAL_OCCUR

------------------------------------------------  -----------

procedure PLCH_TEST_LOOPS  is                        1

  procedure inline_proc (i  number) is               0

     null;                                          10

   for i in 1 .. 10 loop                            11

     res := PLCH_TEST_DETERM('A');                  10

     inline_proc(1);                                 10

end;                                                 1

function PLCH_TEST_DETERM                            0

  return  i;                                         10

end;                                                10

And if uses:

alter session set plsql_optimize_level  = 3;

Then it will:

TEXT                                              TOTAL_OCCUR

------------------------------------------------  -----------

procedure PLCH_TEST_LOOPS  is                        0

  for i in 1 .. 10  loop                            11

    res :=  PLCH_TEST_DETERM('A');                  10

     inline_proc(1);                                 10

end;                                                 1

function PLCH_TEST_DETERM                            0

  return  i;                                          1

end;                                                                    1

And nothing with inline_proc.

Explanation for Slow Performance of VARCHAR2 Overloading?

2011-11-23T03:14:00.001Z

Valentin Nikotin, a PL/SQL Challenge player, recently brought to my attention some unexpectedly slow performance when executing from within a query an overloaded function that accepts a VARCHAR2 argument, compared to a NUMBER argument. I have been able to reproduce this (see below). I thought I'd see if any readers of this blog had an idea as to what might be the cause.

We both ran this code on an 11.2 instance.

CREATE OR REPLACE PACKAGE pkg_test
AS
   FUNCTION fv (i VARCHAR2)
      RETURN NUMBER;

   FUNCTION fn (i NUMBER)
      RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
   FUNCTION fv (i VARCHAR2)
      RETURN NUMBER
   IS
   BEGIN
      RETURN 1;
   END;

   FUNCTION fn (i NUMBER)
      RETURN NUMBER
   IS
   BEGIN
      RETURN 1;
   END;
END;
/

CREATE OR REPLACE FUNCTION fv (i VARCHAR2)
   RETURN NUMBER
IS
BEGIN
   RETURN 1;
END;
/

CREATE OR REPLACE FUNCTION fn (i NUMBER)
   RETURN NUMBER
IS
BEGIN
   RETURN 1;
END;
/

/* No difference in PL/SQL 

"VC2 overloading" completed in: 2.5 seconds
"Number overloading" completed in: 2.52 seconds
"Schema-level VC2" completed in: 2.48 seconds
"Schema-level Number" completed in: 2.49 seconds

*/

DECLARE
   l             NUMBER;
   last_timing   NUMBER := NULL;

   PROCEDURE start_timer
   IS
   BEGIN
      last_timing := DBMS_UTILITY.get_cpu_time;
   END;

   PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
            '"'
         || message_in
         || '" completed in: '
         || (DBMS_UTILITY.get_cpu_time - last_timing) / 100
         || ' seconds');
   END;
BEGIN
   start_timer;

   FOR indx IN 1 .. 10000000
   LOOP
      l := pkg_test.fv (NULL);
   END LOOP;

   show_elapsed_time ('VC2 overloading');
   --
   start_timer;

   FOR indx IN 1 .. 10000000
   LOOP
      l := pkg_test.fn (NULL);
   END LOOP;

   show_elapsed_time ('Number overloading');
   --
   start_timer;

   FOR indx IN 1 .. 10000000
   LOOP
      l := fv (NULL);
   END LOOP;

   show_elapsed_time ('Schema-level VC2');
   --
   start_timer;

   FOR indx IN 1 .. 10000000
   LOOP
      l := fv (NULL);
   END LOOP;

   show_elapsed_time ('Schema-level Number');
END;
/

/* Noticeable difference in SQL 

MAX(PKG_TEST.FV(NULL))
----------------------
                     1
1 row selected.
Elapsed: 00:00:07.93

MAX(FV(NULL))
-------------
            1
1 row selected.
Elapsed: 00:00:05.03

MAX(PKG_TEST.FN(NULL))
----------------------
                     1
1 row selected.
Elapsed: 00:00:04.45

MAX(FN(NULL))
-------------
            1
1 row selected.
Elapsed: 00:00:05.45

*/

SET TIMING ON

CREATE TABLE driver_table
AS
       SELECT LEVEL id
         FROM DUAL
   CONNECT BY LEVEL < 1000000
/

SELECT MAX (pkg_test.fv (NULL)) FROM driver_table
/

SELECT MAX (fv (NULL)) FROM driver_table
/

SELECT MAX (pkg_test.fn (NULL)) FROM driver_table
/

SELECT MAX (fn (NULL)) FROM driver_table
/

DROP driver_table
/

DROP package pkg_test
/

DROP FUNCTION fn
/

DROP FUNCTION fv
/

How Reliable are PL/Scope Results? (9403)

2011-11-10T16:25:00.000Z

The 9 November quiz on PL/Scope asked players to draw conclusions about a package body from the data in the user_identifiers view.

One of our most dedicated players, Iudith Mentzel, spent some time testing out the results one gets from PL/Scope (and queries against user_identifiers) for various uses of labels and GOTOs. I publish her comments below for your consideration.

From Iudith Mentzel

I don't want to object to either the results, which are somewhat "colorful" , or the PL/Scope feature itself, but it looks like the "safe usage" of the feature is at least a little bit "less wider" that one may be (too optimistically) tempted to believe .

While the reasoning behind the answer presented for each choice is completely logical and probably follows the reasoning that the players used, driving categorical conclusions about the code contents by ONLY looking at the data gathered by the PL/SQL Scope feature can sometimes be a little bit dangerous ...

For example:

The LABEL and GOTO issue seems very clear on a first glance, however, here is a small example of what can happen if we "tweak" the code a little bit:

-- amendment to add a GOTO ...
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO all_done;
      NULL;
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

   END do_stuff;
END plch_pkg;
/

-- there is NO LABEL at all in the result set, though we have a label and a GOTO !!!
SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE               USAGE       NAME
------------------ ----------- ------------------------------
ITERATOR           DECLARATION INDX
ITERATOR           REFERENCE   INDX
PACKAGE            DECLARATION PLCH_PKG
PACKAGE            DEFINITION  PLCH_PKG
PROCEDURE          DECLARATION DO_STUFF
PROCEDURE          DEFINITION  DO_STUFF
SYNONYM            CALL        PLITBLM
SYNONYM            REFERENCE   DBMS_SQL
VARIABLE           DECLARATION L_ITEMS
VARIABLE           REFERENCE   L_ITEMS
VARIABLE           REFERENCE   L_ITEMS

11 rows selected.

This is probably because the PL/SQL Optimizing compiler has removed the "non-effective" stuff ...but if we replace the NULL with some other stuff:

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO all_done;
      DBMS_OUTPUT.put_line('Some stuff');
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

   END do_stuff;
END plch_pkg;
/

then the LABEL is back, though the code still performs exactly the same as before!

SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE            USAGE       NAME
--------------- ----------- --------------------
FUNCTION        CALL        SYSDATE
ITERATOR        DECLARATION INDX
ITERATOR        REFERENCE   INDX
LABEL           DECLARATION ALL_DONE
LABEL           REFERENCE   ALL_DONE
PACKAGE         DECLARATION PLCH_PKG
PACKAGE         DEFINITION  PLCH_PKG
PROCEDURE       DECLARATION DO_STUFF
PROCEDURE       DEFINITION  DO_STUFF
SYNONYM         CALL        PLITBLM
SYNONYM         REFERENCE   DBMS_OUTPUT
SYNONYM         REFERENCE   DBMS_SQL
SYNONYM         REFERENCE   DBMS_OUTPUT
VARIABLE        DECLARATION L_ITEMS
VARIABLE        REFERENCE   L_ITEMS
VARIABLE        REFERENCE   L_ITEMS

16 rows selected.

Now I add one more label....

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO all_done;
      NULL;
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

      <<another_label>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

And still no LABEL seen in the output, though we have two labels and one GOTO ...

SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE            USAGE       NAME
--------------- ----------- --------------------
ITERATOR        DECLARATION INDX
ITERATOR        REFERENCE   INDX
PACKAGE         DECLARATION PLCH_PKG
PACKAGE         DEFINITION  PLCH_PKG
PROCEDURE       DECLARATION DO_STUFF
PROCEDURE       DEFINITION  DO_STUFF
SYNONYM         CALL        PLITBLM
SYNONYM         REFERENCE   DBMS_SQL
VARIABLE        DECLARATION L_ITEMS
VARIABLE        REFERENCE   L_ITEMS
VARIABLE        REFERENCE   L_ITEMS

11 rows selected.

However, it all depends on where the label is located, for example:

-- adding still another label, but at the beginning 
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      <<still_another_label>>
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO all_done;
      NULL;
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

      <<another_label>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

-- now the first label only appears ...
SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE               USAGE       NAME
------------------ ----------- ------------------------------
ITERATOR           DECLARATION INDX
ITERATOR           REFERENCE   INDX
LABEL              DECLARATION STILL_ANOTHER_LABEL
PACKAGE            DECLARATION PLCH_PKG
PACKAGE            DEFINITION  PLCH_PKG
PROCEDURE          DECLARATION DO_STUFF
PROCEDURE          DEFINITION  DO_STUFF
SYNONYM            CALL        PLITBLM
SYNONYM            REFERENCE   DBMS_SQL
VARIABLE           DECLARATION L_ITEMS
VARIABLE           REFERENCE   L_ITEMS
VARIABLE           REFERENCE   L_ITEMS

12 rows selected.

Now we have two labels, but still only one LABEL declaration, though they are both "equally uneffective" ...

Below I have two labels, one the target of a GOTO, but still no label appears in the user_identifiers view:

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      GOTO another_label;
      NULL;
      <<all_done>>

      DBMS_OUTPUT.put_line (SYSDATE);      

      <<another_label>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

-- still no label, though here logic does matter !
SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE            USAGE       NAME
--------------- ----------- --------------------
ITERATOR        DECLARATION INDX
ITERATOR        REFERENCE   INDX
PACKAGE         DECLARATION PLCH_PKG
PACKAGE         DEFINITION  PLCH_PKG
PROCEDURE       DECLARATION DO_STUFF
PROCEDURE       DEFINITION  DO_STUFF
SYNONYM         CALL        PLITBLM
SYNONYM         REFERENCE   DBMS_SQL
VARIABLE        DECLARATION L_ITEMS
VARIABLE        REFERENCE   L_ITEMS
VARIABLE        REFERENCE   L_ITEMS

11 rows selected.

The label was effective, but still NOT shown in user_identifiers. The CALL to SYSDATE also NOT shown !!! The reference to DBMS_OUTPUT synonym also NOT shown !!!

I just wanted to emphasize how volatile it is to drive conclusions about source code based ONLY on the results in USER_IDENTIFIERS ...

These results seem to be generated AFTER the compiler optimizes the source code so, at least in some aspects, they may be misleading ...

Regarding the choice that asked about FORALL, though the reasoning behind it seems correct, equally to you and to us, in an after-thought it also can be argued ...and this because using FORALL requires a collection to be used, and that would probably introduce additional data into the USER_IDENTIFIERS result set, whether it is a DBMS_SQL based collection, one based on a locally defined TYPE or even on a type referenced from some other package ...

The output for the sample package shown in the Verification code looks like this:

CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      <<all_done>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

-- we see some SYNONYMS in the output, that were not there in the original quiz
SELECT type, usage, name
  FROM user_identifiers 
 WHERE object_name = 'PLCH_PKG'
 ORDER BY 1, 2
/

TYPE               USAGE       NAME
------------------ ----------- ------------------------------
FUNCTION           CALL        SYSDATE
ITERATOR           DECLARATION INDX
ITERATOR           REFERENCE   INDX
LABEL              DECLARATION ALL_DONE
PACKAGE            DECLARATION PLCH_PKG
PACKAGE            DEFINITION  PLCH_PKG
PROCEDURE          DECLARATION DO_STUFF
PROCEDURE          DEFINITION  DO_STUFF
SYNONYM            CALL        PLITBLM
SYNONYM            REFERENCE   DBMS_OUTPUT
SYNONYM            REFERENCE   DBMS_SQL
VARIABLE           DECLARATION L_ITEMS
VARIABLE           REFERENCE   L_ITEMS
VARIABLE           REFERENCE   L_ITEMS

14 rows selected.

A last remark is about deciding whether a variable is defined at the package level or inside a subprogram: I think this can be done (maybe preferably) by checking whether the "parent" (the context owner) of the variable declaration is the PACKAGE, rather than a subprogram, for example:

--check variable context ownership
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
   g_variable   NUMBER ;

   PROCEDURE do_stuff
   IS
      l_items   DBMS_SQL.number_table;
   BEGIN
      FORALL indx IN 1 .. l_items.COUNT
         UPDATE plch_stuff
            SET amount = l_items (indx);

      <<all_done>>
      DBMS_OUTPUT.put_line (SYSDATE);
   END do_stuff;
END plch_pkg;
/

COLUMN TYPE FORMAT A15
COLUMN NAME FORMAT A20

SELECT var.type, var.usage, var.name, parent.type, parent.usage, parent.name
  FROM user_identifiers  var,
       user_identifiers  parent
 WHERE var.object_name = 'PLCH_PKG'
   AND var.type        = 'VARIABLE'
   AND var.usage       = 'DECLARATION'
   AND parent.object_name = var.object_name
   AND parent.object_type = var.object_type
   AND parent.usage_id    = var.usage_context_id
 ORDER BY 1, 2
/

TYPE            USAGE       NAME                 TYPE            USAGE       NAME
--------------- ----------- -------------------- --------------- ----------- ----------------
VARIABLE        DECLARATION L_ITEMS              PROCEDURE       DEFINITION  DO_STUFF
VARIABLE        DECLARATION G_VARIABLE           PACKAGE         DEFINITION  PLCH_PKG

2 rows selected.

In summary, looking only at the data in USER_IDENTIFIERS, we cannot derive 100% precise (YES/NO) conclusions regarding ALL aspects the code ... Just a few thoughts regarding a tough quiz ... and, as I see, not just for me ...For some reason, it reminds me of the one related to "implicit conversions" in the previous quarter ...

Best Regards,
Iudith

Can Choice Be Correct If Error Raised? (9401)

2011-11-09T13:43:00.000Z

The 7 November quiz tested your knowledge of the fact that this statement:

DROP PACKAGE

will drop both the package specification and body.

Several players complained that we marked this choice as correct :

DROP PACKAGE plch_pkg
/

DROP PACKAGE BODY plch_pkg
/

As one person wrote: "If you drop the a package and then try to drop the package body, you get the error ORA-04043: object PLCH_PKG does not exist. Answer 8571 is NOT correct."

One person even went so as to say: "I really don't like to have the solution DROP PACKAGE plch_pkg / DROP PACKAGE BODY plch_pkg / scored as correct. Yes the correct outcome is shown, BUT it is definitely bad style. And knowing that an error will pop up made me not choosing this answer, so I'm blamed again for good style. I know that you will not rescore the answer. But this kind of scoring makes me think to withdraw from the PL/SQL Challenge. So please harden your choices to support good style as well."

I must admit to being a little taken aback by these responses. Let's first address the "correctness" issue and then move on to what we should and should not include as correct choices in future quizzes.

This choice was marked correct because we asked this:

Which of the choices will result in the following two lines being displayed? [after calling a stored procedure that shows the status of the specified database object]

PACKAGE PLCH_PKG: UNDEFINED
PACKAGE BODY PLCH_PKG: UNDEFINED

It is, without doubt, true that if you run the two DROP statements above (a) those two lines of text will be displayed (so I don't see why a rescoring should be done), as both spec and body are gone, and (b) the second statement will result in Oracle throwing this error:

ORA-04043: object string does not exist
    Cause: An object name was specified that was not recognized by the system.

Now, as noted above, the text will be displayed as required, so I do not think that any changes to the scoring should be performed.

But should I not include such choices in the quiz? And if I do, should I always mark them as incorrect because they are "bad code"?

Certainly, it needs to be very clear that the second statement is unnecessary and will throw the error. More generally, we should provide strong recommendations against using a certain approach if it is problematic in some way.

But never include choices like this marked as "correct"? I just don't see why we would exclude such things. There is usually some sort of lesson to be learned, some way to help you reflect on the way you do your work, and the kinds of traps you can fall into.

In this case, for example, I could see a developer who wasn't clear on the concept that DROP PACKAGE drops the body put such statements in their clean-up scripts. They might never have noticed that errors were being raised, because the end result met their requirements: packages all gone.

So they would in this case have marked that choice as correct, but then learned from reading the explanation that it is not necessary and should therefore be removed. Lesson learned.

What are your thoughts?

Cheers,
Grandpa Steven

Explorations into Pipelined Functions (9398)

2011-11-03T19:28:00.001Z

The 2 November quiz on pipelined table functions and autonomous transactions prompted the following submission from Chad Lee of Telligen, specifically the 2nd and 3rd points in the explanation:

(1) A pipelined table function returns a row to its invoker (a query) with the PIPE ROW statement and then continues to process rows.

(2) Pipelined functions can help improve response time because the entire collection need not be constructed and returned to the server before the query can work with and return a single result row.

(3) In addition, the function consumes less Process Global Area memory, because the object cache need not materialize the entire collection.

I post his comments below for all to consider and discuss:

If one modifies the plch_pipeline package body by adding "dbms_lock.sleep(2);" just before the update statement to introduce a 2 second delay between returning rows from the pipelined function, and creates the following get_sysdate function to return the current sysdate accurate to the second, one can see that the pipelined function does indeed return rows to the calling query in 2 second intervals, as seen in the output. This result does not directly counter (2) or (3).

However, the results of the query are returned all at once, rather than one row every two seconds (think all_rows vs first_rows), which seems to indicate there is some buffering/materializing of the rows due to the autonomous transaction / pipelined function before control is passed fully to the calling SQL statement. If the calling SQL statement was completely free to work with the individual rows returned from the pipelined autonomous transaction (the different get_sysdate value seems to indicate partial control at the calling SQL), wouldn't the rows have been returned to the SQL Plus prompt in 2 second intervals similar to the first_rows hint? This behavior would seem to counter (2) and (3).

If I put the query in a loop in a PL/SQL block, I can see that the loop does not execute until all rows from the pipelined function have been returned. Thus, the rows returned by the pipelined function are being buffered somewhere. Would this not be in the PGA? In effect, isn't the entire collection being materialized before control is returned to the calling program? Does this counter (2) and (3), as the loop does not work with the first row when it is returned, but instead waits for all rows to be materialized before starting execution of the loop?

This behavior reinforces my understanding of Autonomous Transactions, such that although operating outside of the calling transaction, the autonomous transaction must complete fully before control is passed back to the calling transaction. Thus, any data returned by a pipelined function used within an autonomous transaction must in fact be materialized fully before control is passed back to the calling transaction. Whether this occurs one row at a time with a back and forth interaction between the calling transaction and the autonomous transaction or when all rows are computed by the autonomous transaction, a continually growing memory structure would be created. The manner in which the rows are returned would also seem to impact performance, with the back and forth action you describe seemingly slower than returning all at once.

A more in depth example could be created to determine where the pipelined rows are being materialized before the calling transaction continues execution.

CREATE OR REPLACE PACKAGE BODY plch_pipeline
IS
   FUNCTION double_values (dataset refcur_t)
      RETURN numbers_t PIPELINED
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      l_number   NUMBER;
   BEGIN
      LOOP
         FETCH dataset INTO l_number;
         EXIT WHEN dataset%NOTFOUND;

         dbms_lock.sleep(2);      -- add sleep of 2 seconds

         UPDATE plch_parts SET partnum = partnum;
         COMMIT;
         
         PIPE ROW (l_number * 2);
      END LOOP;
      CLOSE dataset;
      RETURN;
   END;
END plch_pipeline;
/

-- generate the current sysdate to the second
create or replace function get_sysdate
return varchar2
is
  ls_date       varchar2(20);
begin
  select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') 
    into ls_date from dual;
  return ls_date;
end;
/

col get_sysdate format a20
SELECT a.column_value, rownum, get_sysdate
  FROM TABLE (plch_pipeline.double_values (
                CURSOR (SELECT line
                          FROM user_source
                         WHERE name = 'PLCH_PIPELINE'
                           AND type = 'PACKAGE'
                           AND line <= 3
                         ORDER BY line))) a
/

Can verify rows are being returned by the autonomous transaction at 2 second intervals. However, the results are all returned at the same time to the SQL Plus prompt and not as they receive their get_sysdate value.

COLUMN_VALUE     ROWNUM GET_SYSDATE
------------ ---------- --------------------
           2          1 11/03/2011 10:33:10
           4          2 11/03/2011 10:33:12
           6          3 11/03/2011 10:33:14

begin
  for rec in (SELECT a.column_value, rownum, get_sysdate date_val
    FROM TABLE (plch_pipeline.double_values (
                CURSOR (SELECT line
                          FROM user_source
                         WHERE name = 'PLCH_PIPELINE'
                           AND type = 'PACKAGE'
                           AND line <= 3
                         ORDER BY line))) a) loop
  
    dbms_lock.sleep(1);
    dbms_output.put_line(
         rec.column_value || ', ' || rec.rownum || ', ' || 
         rec.date_val || ', ' || 
         to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
  end loop;
end;
/

The timestamp generated/printed within the loop has a value after the timestamp generated from the pipelined function, showing that the data from the pipelined autonomous transaction function is being materialized prior to the loop starting execution. */

2, 1, 11/03/2011 11:21:31, 11/03/2011 11:21:36
4, 2, 11/03/2011 11:21:33, 11/03/2011 11:21:37
6, 3, 11/03/2011 11:21:35, 11/03/2011 11:21:38

In addition, if I modify the get_sysdate function to introduce a delay of 3 seconds, one can see from the output that there is a delay of 5 seconds between each row returned from the pipelined function (2 seconds within the pipelined function and 3 seconds from the get_sysdate call), indicating that the entire operation is running in a strictly serial manner. (1 - get value from pipeline function, 2 - make call to get_sysdate for the row in the calling query, repeat these two steps for each of the three rows returned by the pipeline function)

create or replace function get_sysdate
return varchar2
is
  ls_date       varchar2(20);
begin
  dbms_lock.sleep(3);
  select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') 
    into ls_date from dual;
  return ls_date;
end;
/

SELECT a.column_value, rownum, get_sysdate
  FROM TABLE (plch_pipeline.double_values (
                CURSOR (SELECT line
                          FROM user_source
                         WHERE name = 'PLCH_PIPELINE'
                           AND type = 'PACKAGE'
                           AND line <= 3
                         ORDER BY line))) a
/


COLUMN_VALUE     ROWNUM GET_SYSDATE
------------ ---------- --------------------
           2          1 11/03/2011 13:47:54
           4          2 11/03/2011 13:47:59
           6          3 11/03/2011 13:48:04

The same serial behavior is seen in the pl/sql block, with all rows from the pipeline function materialized in a serial manner before executing the cursor loop.

begin
  for rec in (SELECT a.column_value, rownum, get_sysdate date_val
    FROM TABLE (plch_pipeline.double_values (
                CURSOR (SELECT line
                          FROM user_source
                         WHERE name = 'PLCH_PIPELINE'
                           AND type = 'PACKAGE'
                           AND line <= 3
                         ORDER BY line))) a) loop
  
    dbms_lock.sleep(1);
    dbms_output.put_line(
        rec.column_value || ', ' || rec.rownum || ', ' ||
        rec.date_val || ', ' || 
        to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
  end loop;
end;
/

2, 1, 11/03/2011 13:51:45, 11/03/2011 13:51:56
4, 2, 11/03/2011 13:51:50, 11/03/2011 13:51:57
6, 3, 11/03/2011 13:51:55, 11/03/2011 13:51:58

"Average" function not correct due to lack of rounding? (9397)

2011-11-02T16:13:00.000Z

The 1 November quiz tested your knowledge of the ability to select from a nested table using the TABLE operator and, in this case, apply the AVG function to the contents of the collection. The question asked in the quiz was:

Which of the choices implements a function named plch_avg that calculates the average of all elements in a collection of the above type so that the following block displays "3.5" after execution?

We compared the TABLE operator approach to algorithms one might write themselves to compute the average. One of these, marked as correct, is:

CREATE OR REPLACE FUNCTION plch_avg (numbers_in IN plch_numbers_t)
   RETURN NUMBER
IS
   l_index   PLS_INTEGER := numbers_in.FIRST;
   l_average     NUMBER := 0;
BEGIN
   WHILE l_index IS NOT NULL
   LOOP
      l_average := l_average + numbers_in (l_index)/numbers_in.count;
      l_index := numbers_in.NEXT (l_index);
   END LOOP;

   RETURN l_average;
END;

Several players objected and believe that this choice should be marked as incorrect, since it will not always produce the expected result. For example, if I pass a nested table of 9 rows (values 1 through 9), this function returns:

5.00000000000000000000000000000000000001

This is certainly true, and if we asked you to identify algorithms that would work under all circumstances, we would be wrong to mark this as correct. We would also be wrong to mark choice 8476 as correct, since an empty collection would cause the function to fail with a divide-by-zero exception.

But we asked you to identify choices so that the following block displays "3.5". It certainly does that, and so we believe that this choice should be scored as correct.

Your thoughts?

Wanted: Your Ideas for Key PL/SQL Enhancements

2011-10-28T16:55:00.000+01:00

On November 9, I will be doing the keynote presentation at the 100th member meeting of the Northern California OUG. The day before that I will visit with the PL/SQL development team at Oracle HQ.

It's always great to catch up not just with Bryn Llewellyn, the PL/SQL Product Manager, but also some of the developers themselves (those very special human beings who actually build the programming language at the center of so many of our lives). They'll interrogate me to get a sense of what developers are doing (and not doing) with PL/SQL out there in the "real world." And I'll find out whatever I can about new features in the upcoming release of the language (in this case, 12.1).

I usually take advantage of this wonderful opportunity to also tell them about what I'd love to see added to (or fixed in) PL/SQL. Of course, my ideas are limited to my own experience. So I thought I would ask all of you for your ideas.

What changes in PL/SQL would make the biggest difference for you and your applications?

You can reply to this newsletter/blog with your thoughts. You can also visit ILovePLSQLAnd.net to vote on a set of enhancement ideas, and even submit your own for consideration.

I'll pull together all the ideas I receive and present them to the PL/SQL team. But I must warn you: I don't expect to come out of this meeting with a list of confirmed enhancements planned for future PL/SQLs. That simply isn't the way Oracle plays the game. Instead, you'll just have to hold your breath until some future version of Oracle Database delivers the enhancement you requested.

Thanks in advance for your input!
Steven

No Error Log Table, So No Choices Correct? (7990)

2011-10-14T12:54:00.000+01:00

The 13 October quiz tested your knowledge of the DBMS_ERRLOG package and what happens when you try to create an error logging table for a table that has unsupported column types, such as CLOB.

Several players wrote with objections along this line:

I disagree with your answer to this quiz. You say that ORA-00942... propagates unhandled from the block. While this is true, I still selected 'none of the answers are true' because before this error shows, you get the following error when you attempt to execute the create_error_log statement: 'ORA-20069: unsupported column type(s) found: CLOB_VALUE....' so in my opinion, none of the answers are correct.

The question stated "I execute the following statements:" and indeed the execution of this block:

BEGIN
   DBMS_ERRLOG.create_error_log (dml_table_name => 'PLCH_EMPLOYEES');
END;
/

will result in ORA-20069 being raised. But we never stated that each of these statements ran without error, only that we ran them.

Then, because the error logging table was not created, the attempt to use LOG ERRORS with the DML statement causes ORA-00942 (table or view does not exist) to be raised.

My main objective with this quiz was to make you aware that you could get a very puzzling error (table or view does not exist) from a DML statement against a table that clearly does exist. It is not in any way obvious that the error has to do with the fact that the underlying, unnamed error logging table does not exist.

But if you agree that "this is true" - that (to repeat the choice we scored as correct):

The following exception propagates unhandled from the block:

ORA-00942: table or view does not exist

Then I do not see how you can also argue that no choices are correct.

Your thoughts?

Cheers, Steven Feuerstein

Chinese Translations of PL/SQL Challenge Quizzes

2011-10-13T15:12:00.000+01:00

James Su, a PL/SQL Challenge player, recently asked for permission to translate quizzes and post them on a site for Oracle developers working in the Chinese language.

I was, of course, delighted to give him this permission.

You can now check out his work at:

http://www.itpub.net/thread-1499223-1-1.html

If anyone else is interested in translating quizzes, you have my permission in advance - as long as you agree to include links to the PL/SQL Challenge website, so that we can get other people playing the quizzes in "real time."

Typo in Question Text Requires Score Adjustment (7988)

2011-10-12T01:15:00.000+01:00

For the first 11 or so hours of 11 October 2011, we asked players to pick a block of code that would display this string:

1*October*2011*12:01:01

As a number of players noted, none of the choices would do the trick - and they wondered if we'd made a mistake. Yes, indeed, that was a typo. So at 6:15 AM Chicago time, Steven woke up, checked his email and was immediately at all the bug reports.

Then he changed the question text to:

1*October*2011*12*01*01

so that the several hundred other Oracle technologists who would be playing the quiz on the 11th would have a more interesting experience.

As a result of these actions, we will issue a correction to the 189 players who chose "Not correct" for the fourth choice (which should have been correct).

It is, of course, frustrating to everyone when we make mistakes like this, and I (yes, this is me, Steven, talking earlier in the third person) apologize to all. But when I am done apologizing, I like to think about how mistakes like this can happen.

You see, I was especially frustrated this morning because at about 03:00 on the 11th (10 PM Chicago time), I received an email from Jeffrey Kemp, one of the most diligent and expert of our players who also happens to live in Perth and so he plays very early in the day (UTC-timewise). He reported this problem. So I took a look. I looked at the question text. I ran the code for the "correct choice"...

And it looked just fine to me! And so another 8 hours passed, and hundreds of other answers submitted, before I took a closer look and recognized/accepted my error.

How could this be? Because we humans have a tendency to see what we want or expect to see, rather than what is really there. This "quick and dirty" approach to life can be helpful at times, but when it comes to analyzing and debugging code, it is a downright nasty tendency.

Professional proofreaders scan text backwards so that they will not be able to read the text and thereby make all sorts of assumptions, and skip over problems in the text. We programmers can't read our code backwards, so we have to make an extra effort to force ourselves to really look at, really read, what we've written, and make sure it makes sense.

That's what I am going to do from now on. Promise.

Empty String as Index Value in Collection (7987)

2011-10-11T13:29:00.000+01:00

The 10 October 2011 quiz tested your knowledge of the effect of a NULL value being passed to the EXISTS method. Iudith Mentzel did some further analysis and offers up these insights:

First, as probably expected, we cannot use NULL as an array index value, neither for an associative array indexed by PLS_INTEGER nor for one indexed by a VARCHAR2, they both raise VALUE_ERROR:

DECLARE
    my_list   DBMS_SQL.number_table;
BEGIN
    my_list(NULL) := 100;

    IF my_list.EXISTS(NULL) THEN
       DBMS_OUTPUT.PUT_LINE('NULL index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE('NULL index does not exist !');
    END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 4

DECLARE
    TYPE array_t IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
    my_list   array_t;
BEGIN
    my_list(NULL) := 100;

    IF my_list.EXISTS(NULL) THEN
       DBMS_OUTPUT.PUT_LINE('NULL index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE('NULL index does not exist !');
    END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 5

However, for a VARCHAR2 index we can use an empty string ( '' ) as an index without error, and it is NOT the same as a NULL index:

DECLARE
    TYPE array_t IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
    my_list   array_t;
BEGIN
    my_list('') := 100;

    IF my_list.EXISTS(NULL) THEN
       DBMS_OUTPUT.PUT_LINE('NULL index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE('NULL index does not exist !');
    END IF;

    IF my_list.EXISTS('') THEN
       DBMS_OUTPUT.PUT_LINE(' '''' index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE(' '''' index does not exist !');
    END IF;
END;
/

NULL index does not exist !
'' index exists !

This is in spite of the fact that a VARCHAR2 variable having an empty string assigned to it is considered as NULL (which, as we know, is NOT the case for a CHAR variable):

DECLARE
     my_var   VARCHAR2(5) := '' ;
BEGIN
     IF my_var IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('my_var IS NULL');
     ELSE
        DBMS_OUTPUT.PUT_LINE('my_var IS NOT NULL');
     END IF;
END;
/
my_var IS NULL

Strange NULL oddities ...

Without this quiz, no one has probably ever thought of trying to use a NULL or a NULL variable as an array index, just to see what happens ...it is entirely the merit of PL/SQL Challenge to make us dig that deeply :-)

Start and End Dates of the Oracle Magazine Quizzes

2011-10-10T19:15:00.000+01:00

We've received some feedback players expressing confusion over the start and end dates for the Oracle Magazine quizzes and, related to that, when you can see the answers to the quizzes.

Each issue of Oracle Magazine is available in both online and print edition. Generally, the online edition is available one month prior to the print version, or at least prior to the published months for the issue.

In other words, the Sept/Oct 2011 edition of Oracle Magazine can be seen online starting August 1.

So each Oracle Magazine quiz starts one month earlier than the published starting month, and it overlaps (is still open) the quiz for the next edition. This means that:

1. Two Oracle Magazine quizzes are open at any given point in time.

2. You can't see the answers for the quiz for the previous edition even though the quiz for the next/current edition is available for answering.

Cheers,
Steven Feuerstein

Problems with Links to Oracle Documentation

2011-10-08T15:51:00.001+01:00

We have recently spent a fair amount of time linking up questions to the appropriate content in the Oracle documentation set.

Now it looks like Oracle has changed the URLs of every single book in the 11.2 documentation set. Which means that virtually every single one of our links are broken.

We are investigating what happened and if there is any chance these links can be restored. In the meantime, our apology for the broken links.

Practice Makes Expert: New Feature Ready for Testing!

2011-10-07T22:25:00.001+01:00

We are implementing the next set of features for the PL/SQL Challenge and are now ready to invite you to try out the first major enhancement: Practice Makes Expert. Currently, you can take a quiz when it is first offered as part of a competition (daily quiz, weekly quiz, etc.). But we haven't given you any way to take the quiz if you missed its original appearance or if you want to take it again to see if your understanding of the feature has improved.

You can now do that with Practice Makes Expert. To try out the Quiz Practices feature, visit test.plsqlchallenge.com, log in using the same email and password you would use on the production site, and then click on "Practice Makes Expert" button in our "Help Us Test" section:

You will then be taken to a page that explains this feature and gives you an idea of the way it will be implemented on the site. We suggest you read through this page, but after that, you can just click on the Practice menu button to go directly to the Practice Makes Expert Home page, shown below:

You can take the practice quiz on the practice home page or from the Play a Quiz table, by clicking on the new Practice tab:

Rather than explain further about how it this feature works, I'd like to let the website "speak for itself." If you have trouble figuring out what to do, let us know so we can clean up the workflow and/or explanations.

Note: Practice Makes Expert is also the first of a set of upcoming members-only features. We plan to institute an annual membership fee for those who want to support the PL/SQL Challenge project and get even more out of the site. One of the benefits of membership is unlimited practices, and in particular the Autotune practice feature, which automatically sets up practices for you based on criteria you specify.

Other changes at test.plsqlchallenge.com:

Menu Changes: We've moved the links to Feedback, FAQ and Blog off the menu and up to the top right corner as hyperlinks. This frees up space on our menu bar for things like Practice.
Submit Quiz Changes: on the current site, to submit quiz you have to fill out a very long page of fields and sometimes what you enter is lost due to a variety of issues. Now you can use a wizard to submit a quiz. You can enter and submit the minimal amount of information in just a minute or two. You can then continue on to other pages or come back later to finish up your quiz.

We very much want to know what you think of these new features and changes. Feel free to post your feedback here or by clicking on the Feedback link.

Many thanks in advance for your help!
Steven Feuerstein

Participants in Q3 2011 Championship Playoff

2011-10-05T02:17:00.003+01:00

30 September ended the third quarter of 2011, which means....it's time to hold a championship playoff!

Participants come in three "flavors":

Top 25: You ranked in the top 25 for the quarter.
Correctness: Randomly selected from all players whose % correct is at least as high as the lowest % correct of the top 25 and who played at least 45 quizzes in the quarter.
Wildcard: Randomly selected from all players who played at least 30 quizzes in the quarter and whose 30 highest weighted scores meets or exceeds those of the top 25 players.

Visit the FAQ for more details on playoff participant selection.

Below you will find the Q3 2011 participants. Every person on this list devoted a substantial amount of time and brain cells to make it this far. Now they will compete head to head in a time-limited, five-quiz competition to determine the sharpest, most knowledge PL/SQL developers this quarter.

The number in parentheses after their name indicates the number of playoffs in which these players have participated. Congratulations to Jeff Kemp, who has competed in every quarterly playoff since the PL/SQL Challenge started. And kudos to the many other players who are competing for their 3rd, 4th and 5th times!

We will announce the date for the playoff soon.

Name	Rank	Qualification	Country
Frank Schrader (5)	1	Top 25	Germany
Syed Ariful Bari (1)	2	Top 25	Bangladesh
Dennis Klemme (5)	3	Top 25	Germany
Dmitry Pushkashu (1)	4	Top 25	Moldova
mentzel.iudith (4)	5	Top 25	Israel
Viacheslav Stepanov (3)	6	Top 25	Russia
Joaquin Gonzalez (3)	7	Top 25	Spain
Justin Cave (4)	8	Top 25	United States
Randy Gettman (4)	9	Top 25	United States
_Nikotin (3)	10	Top 25	Russia
Michal Cvan (4)	11	Top 25	Slovakia
Rajesh Venkataramani (2)	12	Top 25	India
Janis Baiza (3)	13	Top 25	Latvia
Siim Kask (4)	14	Top 25	Estonia
John Hall (3)	15	Top 25	United States
Niels Hecker (5)	16	Top 25	Germany
Jerry Bull (2)	17	Top 25	United States
Spoon (2)	18	Top 25	Singapore
kowido (3)	19	Top 25	No Country Set
Jeff Kemp (6)	20	Top 25	Australia
Thierry Poels (2)	21	Top 25	Belgium
Ludovic Szewczyk (2)	22	Top 25	Belgium
Chad Lee (2)	23	Top 25	United States
james su (3)	24	Top 25	Canada
Ninoslav Čerkez (1)	25	Top 25	Croatia
Anton Scheffer (2)	30	Correctness	Netherlands
Anna Onishchuk (3)	31	Correctness	Ireland
Yuan Tschang (1)	36	Correctness	United States
ZoltanKekes (1)	40	Correctness	United States
Dejan Topalovic (1)	43	Wildcard	Austria
Carlos Eduardo Mayorga Rodriguez (1)	44	Wildcard	Colombia
sbramhe (2)	65	Wildcard	No Country Set
Alain Boulianne (1)	66	Wildcard	French Republic
Jack Callaham (1)	67	Correctness	United States
macabre (2)	70	Correctness	Russia
Kevan Gelling (3)	132	Correctness	Isle of Man
Michael S. (1)	169	Correctness	Germany
dobloman (1)	192	Correctness	Netherlands
Ashvin Dadhania (1)	302	Correctness	United States

Different parameters for UTL_FILE.FCOPY in 10g and 11g? (7982)

2011-10-04T05:12:00.001+01:00

The 3 October quiz focused on ways to use UTL_FILE package to copy the contents of one file to another file.
Two players wrote with a concern about a possible conflict between the stated minimum version of this quiz (10.2) and the parameter list in the calls to UTL_FILE.FCOPY. Here's one of the comments:

There is an issue with today's quiz on fcopy. The minimum version stated is 10.2, however the formal parameters referred to for utl_file.fcopy are those in 11 - which have changed since. The first four formal parameters have been renamed.

Both of the players indicated that they had ignored what they saw as a problem, and answered it "based on what I think the intention was."

Good move, players! Because in fact there is no difference between the parameter lists of FCOPY in 10.2 and 11.2. The only difference is in the documentation.

In the 10.2 doc, FCOPY is described as:

UTL_FILE.FCOPY (
   location   IN VARCHAR2,
   filename   IN VARCHAR2,
   dest_dir   IN VARCHAR2,
   dest_file  IN VARCHAR2,
   start_line IN PLS_INTEGER DEFAULT 1,
   end_line   IN PLS_INTEGER DEFAULT NULL);

While in the 11.2 doc, FCOPY is documented as having the following parameter list:

UTL_FILE.FCOPY (
   src_location    IN VARCHAR2,
   src_filename    IN VARCHAR2,
   dest_location   IN VARCHAR2,
   dest_filename   IN VARCHAR2,
   start_line      IN BINARY_INTEGER DEFAULT 1,
   end_line        IN BINARY_INTEGER DEFAULT NULL);

But if you actually look at the UTL_FILE package specification in a 10.2 installation (which you can find in the ORACLE_HOME/Rdbms/Admin/utlfile.sql file), you will see that in fact even "way" back in 10.2, the parameter names matched those in 11.2.

Which just proves once again that you are always better off actually checking the code compiled into the database than relying on the (any) documentation!

October SQL Quizzes by Tim Hall, ORACLE-BASE.com!

2011-10-01T12:59:00.000+01:00

As a special treat to our players, Tim Hall of ORACLE-BASE.com fame is providing the SQL quizzes during the month of October. "Oracle ACE of the Year 2006" Tim Hall is an Oracle DBA/Developer and well known Oracle blogger at http://www.oracle-base.com. If that isn't enough, he's written two books and over 400 articles, is an Oracle ACE Director, has presented at numerous user groups and conferences worldwide and recently presented a series of 2-day PL/SQL tuning seminars in Europe and Asia-Pacific as part of the "Oracle University Celebrity Series."

We are extremely pleased to have Tim offer his expertise and teaching skills to the PL/SQL Challenge for the month of October. We hope you enjoy - and are challenged by - Tim's quizzes!

VALUES OF not a valuable and correct solution? (8304)

2011-09-29T13:19:00.002+01:00

The 28 September quiz asked the following:

I must write a procedure that accepts an associative array as a parameter, each of whose elements contains the primary key of a row in that table, and then uses FORALL to update each row identified by one of those primary keys. The associative array may not be densely filled (in other words, there may be index values between the integers returned by the FIRST and LAST methods that are not defined). Which of the choices describe my options for implementing this procedure?

Several players wrote with concerns and questions, most of them having to do with this choice (scored as correct):

Use the VALUES OF clause with the FORALL statement.
Let's address that first, and then touch on the others. Players wrote as follows:

"I set the 'VALUES OF'-answer to wrong because this clause needs and only works with indexes of type PLS_INTEGER. The documentation says: "The index collection must be a nested table, or an associative array indexed by PLS_INTEGER or BINARY_INTEGER, whose elements are also PLS_INTEGER or BINARY_INTEGER." You said nothing about type of the index of the associative array, so I thought there are some cases where this solution does not work "

"The last answer, using the "values of clause" is scored as correct, but you need to write some extra code to populate a second collection. Using the same logic the second answer, You will not be able to use FORALL, would be valid choice too. Isn't it?"

"Hello, I don't agree with the scoring of this quiz for the choice 4 ("Use the VALUES OF clause with the FORALL statement.") - I thought this was a trick question since FORALL ... VALUES OF ... cannot be used without the second collection, which was not mentioned in the answer, whereas FORALL ... INDICES OF ... can be used directly on our collection. It would have been better if the answer for choice 4 had some mentioning of the second collection (although it would have hinted that it was a correct answer). Based on the first choice, I expected the wording for the fourth choice to be something similar ("Before executing the FORALL statement populate a second collection with the index values placed in its elements. Then use the VALUES OF clause with the FORALL statement"). Actually, this is not a score adjustment request, just a comment."

My thoughts: I suppose this is yet another example of the difficulties one may encounter with an "all words" quiz (as oppose to one based on specific code). The objective of the quiz was to make sure developers are aware of INDICES OF and VALUES OF as FORALL clauses that allow you to reference a sparse bind array in the FORALL's DML statement.

The question does not specify any restrictions on how the procedure will implement the use of FORALL. It does not say that no other code can be written, no other collections can be defined and used. So from that standpoint, almost any choice (except something on the order of "You will not be able to use FORALL in this scenario.") would need to be scored as correct. Which we did.

So I do not see why the VALUES OF choice would need to be scored as incorrect.

Other comments and my responses:

"1. The question refers to "that table" but didn't mention a table beforehand. 2. The word "element" might be misinterpreted as referring to either the indexes or the values in the associative array."

My response: yes "that table" was a typo. I will fix it, but I don't think it justifies a rescore. Element and index values: these two terms are used by Oracle in its documentation. An element is never used to refer to the index value, that I know of.

"In the PL/SQL Quiz from 2011-09-28 you stated:"I must write a procedure that accepts an associative array as a parameter, each of whose elements contains the primary key of a row in that table...". This is a litle bit confusing for me, because I took into account the possibility, that primary key may also consist of multiple columns, not only single column. Unfortunately, the question was for me not so clearly defined... Please, could you explain, which solution would be correct, if there is a multiple columns primary key"

My response: another very excellent point regarding the potential drawbacks of an "only words" quiz! Yes, I did not think about multiple column primary keys. That would certainly affect and complicate the implementation of the procedure (assuming it was a collection of records, you would have to write very different code in pre-Oracle11g versions, since you cannot reference a field of a collection-based record inside a FORALL until then), but my same point applies from above: I do not specify any restrictions on how the procedure is implemented, except that it use FORALL. So the correctness of choices would still not change.

Well, that's my view on the 28 September quiz. How about you?

Recent Quizzes from the PL/SQL Challenge

2011-09-27T16:35:00.001+01:00

In the month of September (to date), over 1260 Oracle technologists submitted 14,360 answers to quizzes, and explored the library of past quizzes. Here is a selection of quizzes from this month:

Oracle PL/SQL Quizzes

SQL Quizzes

Here are the SQL quizzes played in September: 3 September 2011: Use of analytic function COUNT with the RANGE clause to count records with values within defined ranges.
520 Players ** Avg. Correct: 82% ** Avg. Time: 2082 seconds ** Rating: 4 stars

10 September 2011: Partitioned outer join is one of the most useful data densification techniques available since Oracle10g.
526 Players ** Avg. Correct: 61% ** Avg. Time: 4645 seconds ** Rating: 4 stars

17 September 2011: Use of analytic function RATIO_TO_REPORT to show "percent of total" in select statements.
474 Players ** Avg. Correct: 72% ** Avg. Time: 2931 seconds ** Rating: 4 stars

APEX Quizzes

Here are the APEX quizzes played in September:

3 September 2011: This question will teach users how the APEX data dictionary views can be used to find out information about the applications in the workspace.
223 Players ** Avg. Correct: 59% ** Avg. Time: 925 seconds ** Rating: 4 stars

10 September 2011: Shows different methods of customising error messages inside APEX that will save time repeating code.
199 Players ** Avg. Correct: 56% ** Avg. Time: 208 seconds ** Rating: 4 stars

17 September 2011: Learm how to correctly send emails using the apex_mail.send procedure.
206 Players ** Avg. Correct: 66% ** Avg. Time: 2206 seconds ** Rating: 4 stars

What's the value of using %TYPE for parameters?

2011-09-26T17:21:00.000+01:00

I received this question from a player last week:

This is not a question about the 13 September quiz in particular, but a related issue that it raises in my mind. I know that I should never do this:

l_myval VARCHAR2(100);

when I can do this:

l_myval mytab.col1%TYPE;

But what about parameters? Until Tuesday's quiz, I assumed that the constraints on subtypes and anchored parameter would be checked. Now I see that they're not. So is there any advantage in writing

PROCEDURE myproc(p_in IN mytable.col1%TYPE)

instead of:

PROCEDURE myproc(p_in IN VARCHAR2)

? The only advantage that I can see is that the first version might make it more obvious how the procedure is intended to be used.

Rather than answer with my views immediately, I thought I'd offer it up to our players to see what you think.

Players report disabled RESULT CACHE...why?

2011-09-24T13:01:00.000+01:00

The 14 September quiz tested players' knowledge of the Oracle Database 11g function result cache feature.

Two players wrote to say that their tests did not reproduce the results claimed by our verification code.

It seemed as though the result cache was not working for them. They are both running Enterprise Edition. One player reported the following:

SQL> show parameter result_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 0
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

SQL> ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE =128M;

System altered.

SQL> show parameter result_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 0
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

SQL> select dbms_result_cache.status from dual;

STATUS
--------------------------------------------------------------------------------
BYPASS

My Result Cache is disabled and after repeated tries, I am unable to enable it and have no clue how I can fix this.

I am at a loss to explain to these players how to get the result cache feature enabled. So I thought I would invite the PL/SQL Challenge community to offer its wisdom. Any ideas?

"Could" Changes Really Improve Performance? (7755)

2011-09-23T12:19:00.000+01:00

The 22 September quiz asked you to analyze a piece of code and decide:

Which of the choices describe a change I can make to the plch_emp_loop procedure that will not affect the external behavior of the procedure (what it displays or which rows it updates), but could improve its performance?

The quiz was intended to be about your ability to identify loop invariants, expressions within the body of the loop whose outcomes do not change with each execution of the loop. A standard optimization step is to replace the invariants with a variable (or better yet a constant!) that is not re-evaluated with each iteration of the loop.

Several players wrote with concerns about the way this question was worded and scored:

1. Hi Steven, I usually do not complain about quizzes, but I felt that yesterdays quiz was a little bit vague and not up to par or standards of the plsql challenge. First thing is how the question is formed. I don't like vague words like could to be in a quiz. The should always be will, specially when it comes to performance enhancements. But looking at the question itself. "Which of the choices describe a change I can make to the plch_emp_loop procedure that will not affect the external behavior of the procedure (what it displays or which rows it updates), but could improve its performance?". What if all the data in the table have salaries less that the minimum. Then this procedure never runs and the enhancements have no effect at all anyways. 2 answer came out wrong for me, probably because I emphasized too much on the performance part of the question rather that reading the code itself. Therefore I fell into the SYSDATE trap, marking it as correct. It does improve performance, there is no doubt of that, but it of course is a changing variable and therefore will change the internal running of the program. The other one was the number 8123 "l_year NUMBER := TO_CHAR (date_in, 'YYYY');" This will make the IF statement later an implicit conversion (CHAR => NUMBER), that will not neccessarily run faster and therefore I don't think it could be a correct answer.

2. How much performance gain do you expect to get from changing from the explicit conversion TO_CHAR (date_in, 'YYYY') to the implicit conversion of l_year? I tried it, and I didn't get any significant gain.

3. Answer 3 and 4 of the 22 sept quiz contradict! On 31-12-2010 to 01-01-2011 the year changes. PS answer 3 makes an assumption about what date should be used and that changing de date is the best programming solution.

4. Option 3 was scored as incorrect: " The question did not include any constraints on when and for how long this program runs. If during the execution of the procedure, the hour changes, then we cannot safely extract the TO_CHAR (SYSDATE, 'HH24') expression and evaluate it just once at the start of the procedure." This statement is correct but I think this is not a very good example. It is a correct that the hour can change during the running of the program, but do you want to have just a part of your employees updated during a run of the program; I don't think so!

5. An answer in quiz 5142 states "The question did not include any constraints on when and for how long this program runs", but the quiz question says "never takes more than 4 hours to complete" - so I would have thought it would be safe to capture l_hour - although I guess it depends on when the program was launched. I was also curious about the affect on performance on the function call to plch_config.min_salary, since it is just an assignment, no conversion required - performance gain almost nominal?

I will offer some responses here, but then open it up for discussion from players.

I used the word "could" because I wanted players to focus on a logical analysis of the code (what in theory could improve performance? and not so much on analyzing the actual gains of specific changes, which as some noted could be very minimal indeed).
Excellent point about the change to l_year resulting in an implicit conversion, when previously there was no conversion. I did not catch that and I believe it makes that choice ambiguous, not clearly a net performance gain when the loop invariant is extracted. I think I will probably need to issue a correction for that choice.
The answer for choice 3 was incorrect and has been changed to remove the leading sentence. I added time constraint text to the question, but forgot to remove this from the answer.
I do not see a contradiction between answers 3 and 4 (8122 and 8123 to use their IDs).The year does not change while the program executes, based on " it is only executed during the first month of each quarter (January, April, July and October) and never takes more than 4 hours to complete".

Other comments and points of analysis?

New Poll on Changing Question Type

2011-09-14T12:29:00.000+01:00

The type of question for the 13 September quiz was changed from "only one choice correct" to a normal multiple choice question after the quiz was closed. This was done because a player noted the following:

"This is a prime example of a quiz that should not be "at most one" correct choice. More than one concept is being tested; however, if a player is weak in any part then the player receives no credit. The results of the quiz would have been more indicative of players' relative knowledge had each exception scenario been presented as an independent selection."

And we agreed.

But another player then objected to this change, saying: "This is unfair towards the players who consumed extra time to try to answer correctly, for avoiding scoring zero points."

Our feeling is that you should be making your best effort to answer the question correctly, regardless of the type of question, and so if we make a mistake in how we set up the question, we should fix it. But we are not competing in the daily quiz, so we may not understand well how all of our players see this issue.

We have set up a poll on the PL/SQL Challenge website to find out how you feel about it. Please take the poll and/or add your thoughts to this posting.

Cheers,
Steven Feuerstein

New Play a Quiz Features

2011-09-12T17:42:00.000+01:00

As explained in a previous blog posting, the Play a Quiz table on the home page has gotten very full and busy. Players have been complaining about not being able to find the quizzes they need to take, or recently took.

So over the weekend we put in place a number of changes that should help you:

1. Moved Play a Quiz to the top of the page
2. Save filter setting as default
3. Quick access to My Technologies
4. Separate tab for Taken/Closed quizzes

Taken altogether, the home page now looks like this:

The Not Taken tab will show you all quizzesthat are open (can be played). Note that you might not be eligible to play all of them (such as the quarterly playoff), but for the most part they will be precisely those quizzes you can play. Quizzes are ordered by those that will be over soonest.

The Taken/Closed tab shows you only those quizzes that you have taken or are closed (you missed the chance to take them). Those quizzes you answered most recently appear at the top of the list.

If you only want to see quizzes for technologies in which you are interested, change the filter to My Technologies:

And if you don't want to have to change this filter setting each time you visit the home page, click on "Save Filter as Default".

Finally, if you want to change any of the settings for the technologies in which you are interested, click on "Edit My Technologies" to take you directly to the Technologies to Play page of your account.

We hope these changes make it easier to play your quizzes and review the results after you've played.

IE8 and Compatibility View: TURN IT OFF!

2011-09-12T17:30:00.000+01:00

While we continue to have problems displaying the plsqlchallenge.com site on IE7, I was particularly dismayed to receive a bug report regarding IE8. A player reported that when he visited the home page, it looked like this:

Not good.

And what bothered me most of all is that on my laptop, PLSQLChallenge.com works just fine on IE8.

Well, it took a little bit of investigation, but we finally discovered that if you have Compatibility View turned on in IE8 (and, likely, in IE9 as well), you end up with a home page like that shown to the left.

If, on the other hand, I turn off Compatibility View, the home page displays without any problem. So if your home page looks weird, check this setting before you submit a bug report:

PL/SQL Challenge Activity Summary for 5 September 2011 - 9 September 2011

2011-09-12T13:12:00.002+01:00

Mysteries of Implicit Conversions (7254)

2011-09-09T18:01:00.000+01:00

The 8 August quiz asked you to determine how many conversions Oracle performs for us implicitly when the following block is executed:

DECLARE
   l_timestamp   TIMESTAMP := '2011-08-01';
   l_increment   PLS_INTEGER := SUBSTR ('10x10=100', 4, '2');
BEGIN
   DBMS_OUTPUT.put_line (
      TO_DATE (l_timestamp) + (l_increment || 0));
END;
/

We scored 8 as the correct answer. Two players wrote with the following observations:

1. The strange thing here is the fact that over the different versions, Oracle has changed the overloadings of PUT_LINE in the DBMS_OUTPUT package. Specifically, in Oracle 7.3.4.0.0 there were 3 overloads, for VARCHAR2, NUMBER and DATE. This was even explicitly mentioned in the following: http://www.en8848.com.cn/reilly%20books/books/oracle/advprog/ch02_08.htm In Oracle 8.1.7.4.1, there remained only two overloads, for VARCHAR2 and NUMBER. In Oracle 10g and higher there remained only a single overload, for VARCHAR2. Why did Oracle do this ? Just for introducing an additional implicit conversion ? I even remember a version where using DBMS_OUTPUT.put_line( a_number ) used to fail, and you had to use an explicit DBMS_OUTPUT.put_line( TO_CHAR(a_number) ). In the older versions, when the overloads exist, the argument passed was indeed stored in the DBMS_OUTPUT buffer in its native data type, and only converted to a string when retrieved for display (ex. by SQL*PLUS ). This one is probably the one that many players have missed ... In fact, we use this "bad practice" day by day in the quiz, DBMS_OUTPUT.put_line is the top star of each pl/sql block ... Does anybody think that we should change this ? I would hardly believe it ...

By the way, enabling the PLSQL_WARNINGS do NOT produce any warnings for any of these 8 implicit conversions, strange by itself ... ( tested in Oracle 11.1.0.7.0 ).

Anyway, this quiz was a too high price payed for a small issue ... Like in the last playoff, 0 or 7 versus the correct 8 were scored the same :( :( ... which is not completely right in my opinion, because here recognizing each implicit conversion by itself has its own value as "piece of knowledge", and cannot/should not be completely cancelled ... Specifically for such "total" quizzes, better than making them single choice is to allow for different choices with different "weights" (and scoring), that would reflect knowledge more accurately.

2. I think there may be two additional implicit conversions that are taking place, revolving around the Date + Number in the dbms_output.put_line statement. Although it is often said that dates are internally stored in a numeric format, it is not the same format as a numeric datatype (See reference to Concepts manual). In addition, although the (+) operation can be used with date variables, the actual (+) operation only takes place with numeric datatypes or any datatype that can be implicitly converted to a numeric datatype (see reference SQL Reference manual). Thus, to perform the operation Date + Number, Oracle is internally converting the Date to a number, performing the addition operation, and then converting the result back to a Date. This implicit conversion is different from the others referenced in the question, as there is no (to_xxx) operation that can explicitly perform this operation; it is all done internally to perform (+) operations with Date datatypes. Thus, the total number of implicit conversions for the block of code is 10.

From 10.2 Concepts Manual Chapter 26 "Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision." "Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second." From 10.2 SQL Reference Chapter 4, page 3. "You can use an arithmetic operator with one or two arguments to negate, add, subtract, multiply, and divide numeric values. Some of these operators are also used in datetime and interval arithmetic. The arguments to the operator must resolve to numeric datatypes or to any datatype that can be implicitly converted to a numeric datatype."

Your thoughts?

SAVE EXCEPTIONS and LOG ERRORS: objections raised (7253)

2011-09-08T23:51:00.000+01:00

The 7 September quiz asked you to compare and contrast the use of SAVE EXCEPTIONS and LOG ERRORS, two mechanisms for continuing past errors raised in DML statements.

Three players wrote with objections, two concerning this choice, which was scored as correct:

When you use SAVE EXCEPTION with FORALL statement, if any of the DML statements fail, changes made to rows by those DML statements are rolled back. When you use LOG ERRORS with your DML statement and that statement fails, Oracle will not necessarily roll back all changes to rows modified by that statement.

1. "I think that this choice is scored wrongly. If we use FORALL with SAVE EXCEPTIONS, the changes performed by the previous DML-s ( the previous successful iterations of the same FORALL statement ) ARE NOT rolled back."

2. "I suppose, that when you use "LOG ERRORS" clause, words "statement fails" mean only one thing: that number of invalid records have exceeded LIMIT value, and statement immediately stopped with exception. In that case, as I know, Oracle will necessarily roll back all changes to rows modified by that statement. In other hand, when number of erroneous records has not reached LIMIT value, your statement does not fail, it finished successfully."

My response to #1: I don't see this point. We are saying in the choice that the changes made by any failed statement are rolled back. We don't even talk about successful statements - and you are right, those are not rolled back.

My response to #2: ARGH! I think you make a very good point; we were a bit sloppy with words here. The DML statement as a whole does not fail, just because an attempt to change a row identified by that statement fails. The statement, in fact, continues to execute.

One player also wrote regarding this choice:

You can use FORALL SAVE EXCEPTIONS with dynamic SQL, but LOG ERRORS may only be used with static DML statements.

He said: "In the answer 4 example, FORALL is NOT part of the dynamic DML. So I doubt the question can be answered in a clear way given this example. I guess my objection will be overruled by pointing out the choice of the phrase "used with dynamic DML". Well, my grandmother could cook a great meal WITH dynamic DML, if necessary."

My response: I don't really get it. You are right; FORALL is not part of either the static or dynamic DML statement that is "attached" to the FORALL header. But my sentence doesn't claim that it is, does it?

It simply asserts that you can do this

FORALL ...
SAVE EXCEPTIONS
EXECUTE IMMEDIATE my_dynamic_dml;

That is, I can "use" FORALL SAVE EXCEPTIONS to execute a dynamic SQL statement.

I am interested to hear your comments; at this moment, though, I am inclined to issue a re-scoring on choice #483, and correct the verbiage to the following (change in bold):

When you use SAVE EXCEPTION with FORALL statement, if any of the DML statements fail, changes made to rows by those DML statements are rolled back. When you use LOG ERRORS with your DML statement and an attempt to update a row fails, Oracle will not necessarily roll back all changes to rows modified by that statement.

Finding Quizzes in Play a Quiz

2011-09-06T15:39:00.002+01:00

Since the upgrade to v2 of the PL/SQL Challenge quiz platform, we have taken advantage of its flexibility to add several new quizzes: weekly SQL, PL/SQL and logic quizzes; the newest, Toad for Oracle weekly quiz; the Oracle Magazine PL/SQL 101 quiz.

As a result, the Play a Quiz table on the home page has gotten very full and busy. Players have been complaining about not being able to find the quizzes they need to take, or recently took. We are looking at a number of changes to avoid this confusion. In the meantime, here is an explanation of the steps you can take right now to gain clarity.

The Play a Quiz table has two tabs: Current and Upcoming. Current shows you all open quizzes that you can take, as well as some that you recently completed. You can use the Filter to restrict which quizzes are shown. You can also see other "current" quizzes by clicking on the right arrow in the bottom right corner of the table. Finally, the "View All Competitions" will take you to an Interactive Report that will allow you to search for any quiz that has already been played.

Use the filter to show quizzes for a specific technology, only those that are still open or only those for a technology in which you have expressed interest ("My Technologies"; more on this below).

My Technologies

As you can see, we offer quizzes for PL/SQL, SQL, APEX, Logic and Toad for Oracle (with more likely to come!). By default, we show quizzes for all technologies on the page. And if you take a quiz for one of these technologies, we automatically update your profile, adding this technology to your list.

If you choose "My Technologies" in the filter list, then only those quizzes for your technologies will appear. So if you are not an APEX developer and you've never played an APEX quiz, that is the quickest way to make APEX quizzes disappear from Play a Quiz.

Note: we will likely add the ability to save your Filter by selection so you do not have to keep changing it when you get to the Home page.

To modify your technology list, visit your Account (click on "Account" in top right corner) and then click on "Technologies for Play":

You can then add or remove the technologies as desired:

I hope this information helps you get the most out of the PL/SQL Challenge generally, and the Play a Quiz table more specifically.

Please offer any suggestions you have for improving how this works.

Cheers, Steven

New weekly quiz on Toad for Oracle!

2011-09-05T12:51:00.000+01:00

Bob Marley tells us in Coming In From the Cold: "Why do you look so sad and foresaken / When one
door is closed / Don't you know another is open."

OK, so summer is coming to an end. And it can make us sad to think about colder weather (for those of us up north, anyway!) approaching. So, yes, the "door" of warmth and sun may be closing....but another door at the PL/SQL Challenge is opening: a new quiz launches in September 2011: the Toad for Oracle Weekly Quiz!

Starting on September 3, the PL/SQL Challenge will host a weekly quiz on Toad for Oracle, sponsored by Quest Software. Toad for Oracle is used by hundreds of thousands of Oracle technologists every day. Everyone has their favorite features and there's always more to learn about this incredibly useful tool. Now, you can test,
demonstrate and deepen your expertise in Toad for Oracle with the Weekly Toad Quiz!

Your answers will be ranked, based on correctness and time to answer. Top ranked players will be displayed at ToadWorld and Quest will raffle off ebooks of the Oracle PL/SQL Language Pocket Reference from O'Reilly Media to all those who played the quiz that month.

So if you are a Toad user, this is the perfect opportunity to show how well you know this tool, and learn more about what Toad for Oracle can do for you.

Daily PL/SQL Quizzes between 22 August and 26 August

2011-08-30T18:08:00.000+01:00

Last week, 841 Oracle technologists submitted 2,548 answers to quizzes. Here are the PL/SQL quizzes played that week:

22 August 2011: When you use the FOR UPDATE clause in your query, Oracle will lock all rows identified by the SELECT statement. When you specify columns with FOR UPDATE OF, locks will be placed only on rows in tables whose columns are in the list.
              620 Players ♦ Avg. Correct: 79% ♦ Avg. Time: 325 seconds ♦ Rating: 4 stars

23 August 2011: When writing a CASE statement, you should always include an ELSE clause - unless you are absolutely sure that one of the WHEN clauses will always evaluate to TRUE. That's a big assumption to make.
              644 Players ♦ Avg. Correct: 54% ♦ Avg. Time: 110 seconds ♦ Rating: 4 stars

24 August 2011: When you initialize an object type instance, you must supply an expression for each attribute. When you initialize a nested table or varray, you do not need to supply any expressions (elements) for the array.
              670 Players ♦ Avg. Correct: 75% ♦ Avg. Time: 335 seconds ♦ Rating: 4 stars

25 August 2011: Use TRUNC to remove the specified number of significant digits from a number. Use TRUNC, in particular, when you do not want rounding to be performed on the number.
              661 Players ♦ Avg. Correct: 87% ♦ Avg. Time: 271 seconds ♦ Rating: 4 stars

26 August 2011: The value of the LAST_DDL_TIME column of the ALL_OBJECTS and USER_OBJECTS data dictionary views is updated whenever the object is changed (for example, when code is changed and then compiled). But this column value is not changed if you replace a program and the code has not been modified.
              627 Players ♦ Avg. Correct: 24% ♦ Avg. Time: 425 seconds ♦ Rating: 4 stars

Impact of NULLs and Analyzing Choice Correctness (5926)

2011-08-29T18:47:00.000+01:00

The SQL quiz that started on 13 August has prompted a number of players to raise questions about the impact of NULL values (sorry about the delay in writing about this!). And, though it might seem odd, their comments also relate to how you should read, interpret and choose correct answers for our quizzes.

The quiz asked the following question of the plch_employees table:

We want to retrieve the total earnings (i.e. salary + commission) of an employee. Which queries give us the correct result?

The rows in the table included some NULL values for commission, while none of the salary values were NULL.

Players questioned our scoring as correct any of the choices, because all the queries assumed that the salary will never be NULL.

I will make a few comments, and then invite both our SQL author/reviewers and players to offer their own perspectives. My comments:

1. It would have been better if we'd put a NOT NULL constraint on the employee_salary column. That would avoid the issue altogether (in fact, I plan to do this after we finish this discussion).

2. It is important to draw a distinction between answering a quiz and developing "bullet-proof" code that handles all possible scenarios.

When you take a quiz, you answer the question in the context of the information provided in that question - and nothing else. In this case, none of the salaries were NULL and so the issue of the impact of NULL on a formula like "salary + commission" only affects the commission "side" of the formula.

So I do not believe that any change in scoring needs to take place. But, as noted earlier, I do think we should add a NOT NULL constraint on the salary column.

Your thoughts?

PL/SQL Challenge upgrades to APEX 4.1

2011-08-29T16:35:00.000+01:00

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)

2011-08-26T13:02:00.000+01:00

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

2011-08-24T13:15:00.000+01:00

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

2011-08-23T15:19:00.000+01:00

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)

2011-08-18T14:40:00.000+01:00

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)

2011-08-17T01:35:00.000+01:00

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

2011-08-15T12:53:00.001+01:00

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)

2011-08-12T17:51:00.000+01:00

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?

FORALL, UPDATE and Reality-Doc Conflicts (6431)

2011-08-10T18:37:00.000+01:00

The 9 August quiz tested your knowledge of the restriction (prior to Oracle 11g) that you cannot reference fields of collections of records in a FORALL statement. The FORALL statement we showed in the question is this:

FORALL i IN 1 .. l_tab.COUNT
      UPDATE plch_employees
         SET salary = l_tab (i).salary * 1.1
       WHERE employee_id = l_tab (i).employee_id;

Notice that the same collection (l_tab) is referenced in both the SET and WHERE clauses.

A player who pays close attention to the Oracle documentation wrote as follows:

For both 11.1 and 11.2, the "PL/SQL Language Reference" documentations (as PDF, for 11.1 page 13-59, for 11.2 page 13-79) states the following: "If dml_statement is an UPDATE statement, its SET and WHERE clauses cannot reference the same collection."

Well, I must admit, this was news to me. I'd never noticed that restriction and, it turns out, for a fairly good reason: neither the PL/SQL compiler nor run-time engine complains about this scenario at all.

So is the documentation correct or is the functionality of PL/SQL correct? I asked Bryn Llewellyn, PL/SQL Product Manager. Here is his reply:

"I agree that it [my demonstration code] does seem to be at odds with the claim from the docs that you quote. 'll have to ask about this and get back to you."

Bryn also provided a script (see below) that he is using to reproduce the issue.

Needless to say, I do not plan to change the scoring of the question!

CLEAR SCREEN
CONNECT Sys/Sys AS SYSDBA
declare
  procedure Create_User(Who in varchar2) is
    User_Does_Not_Exist exception;
    pragma Exception_Init(User_Does_Not_Exist, -01918);
  begin
    begin
      execute immediate 'drop user Usr cascade';
    exception when User_Does_Not_Exist then null; end;
    execute immediate '
      grant
        Unlimited Tablespace,
        Create Session,
        Create Table,
        Create Trigger
      to Usr identified by p';
  end Create_User; 
begin
  Create_User('Usr');
end;
/
CONNECT Usr/p
create table t(ID integer primary key, c1 varchar2(10), c2 number)
/
begin
  insert into t values(1, 'a', 10);
  insert into t values(2, 'b', 10);
  insert into t values(3, 'c', 30);
  commit;
end;
/
select ID, c1, c2 from t
/
declare
  type x is table of number;
  v x;
begin
  select c2
  bulk collect into v
  from t
  for update;

  forall i in 1..v.Count()
  update t
  set c2 = v(i)*2
  where c2 = v(i);
end;
/
select ID, c1, c2 from t
/

Is typing and running code a form of cheating?

2011-08-05T20:12:00.000+01:00

I received this email from a player today:

Is it okay to use sqlplus at all while taking a quiz? I know reading oracle documentation is acceptable, and typing the quiz in sqlplus verbatim is not allowed, but are we allowed to test basic concepts in sqlplus at all? I have been assuming it is not allowed. For example, I wasn't sure if IS OF type returned true if the value of the variable was the type or if the variable was the type. The documentation isn't always clear. If I was allowed to use sqlplus, I would have tested out a few statements to verify the expected outcome of that statement.

The answer is: it is perfectly all right to use SQL*Plus, Toad, SQL Developer, PL/SQL Developer, etc. to write and execute code to help you answer the quiz.

You should not (and, in fact, you should not be able to) copy and paste code from a quiz, and then simply execute it. But as I note, you should not be able to do this; most quizzes present code as an image. If the code is accessible for copying, then it is not (or, shall I say, should not be) a competitive quiz (rankings and prizes) and we don't really care if you copy and paste.

But if you want to take the time to type in a block of code (or more), then execute that code to see the results, please go right ahead. The most important objective of the PL/SQL Challenge is to improve your knowledge of PL/SQL (and SQL and APEX and....). I can think of few better ways to do this then to write code.

You will, of course, be "penalized" for taking the time to write code; your score will be reduced by the extra time required to do so. But it is definitely not cheating.

NLS Settings and Assumptions - Be Careful! (5187)

2011-08-04T12:20:00.000+01:00

The 3 August quiz tested your knowledge of the way that Oracle performs implicit conversions, in particular in this block:

DECLARE
   n   NUMBER;
BEGIN
   n := '123.456' || 900;
   DBMS_OUTPUT.put_line ('X' || n || 'X');
END;
/

We reported the correct answer as:

X123.4569X

But several players wrote to say that when they tested our answers with the verification code, they received the following error:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

So did we score this quiz improperly? Another player offered an explanation:

Hi, stupid me... A quick test for yesterdays quiz resulted in "ORA-06502: PL/SQL: numeric or value error: character to number conversion error". And that's the answer I gave for the quiz. Today I received the results and it seems I got it wrong... Of course I got it wrong...I live not in America, so the NLS setting were not set accordingly. I know they are in the assumptions, but after 100 quizes of not needing them, they'll get out of your mind.

And that is precisely why the error was not raised in the verification code and why the quiz was scored correctly. The assumptions state that:

The following calls to DBMS_SESSION.SET_NLS define national settings in effect for a quiz, unless otherwise specified in the quiz. If your local environment uses different settings, you may execute these statements to ensure compatibility with behavior and output of executable code provided by the PL/SQL Challenge.

DBMS_SESSION.SET_NLS ('NLS_LANGUAGE' ,'AMERICAN');
DBMS_SESSION.SET_NLS ('NLS_TERRITORY','AMERICA');
DBMS_SESSION.SET_NLS ('NLS_CALENDAR' ,'Gregorian');

This player also offered the following suggestion:

Is it a good idea when asking a question depending to NLS settings, to repeat this in the question(So people who want to test it and not living in the US aren't disadvantaged)? Just an idea.

That is a good idea and we will strive to remember to do this.

Public Profile Settings for Your PL/SQL Challenge Account

2011-08-03T15:44:00.000+01:00

A player wrote to us today:

I completed every daily PL/SQL challenge (as well as every Weekly SQL quiz) in July, 2011. Yet I did not receive the Achievement, "All Quizzes Taken in July 2011 (PL/SQL Challenge)" on my profile. Yet I notice that others have received this achievement. Please rectify. (Note: I did not take any of the APEX or Logic quizzes.) Thanks; your site is great.

I checked his public profile and found that he had not enabled the display of achievements. Now his accomplishment is visible to anyone who checks out his profile. And that reminded me that I should remind all of you:

While you can register by providing the absolute minimum of information, we allow you to set up your profile so that you display your technical experience, professional achievements, recommendations to others, and more. You also have complete control over what information is displayed and what is not.

I strongly encourage you to take a few minutes to visit your Account page, enter this information, and then click on the Public Profile Settings to ensure that others can what you have entered:

You can then decide which of the information from your profile you want to make public:

New Oracle Magazine Quiz Starts Today!

2011-08-01T12:23:00.001+01:00

Today marks the start of a new quiz at the PL/SQL Challenge: the Oracle Magazine PL/SQL quiz. I write a column for Oracle Magazine and two issues ago, I started a series of articles that introduce PL/SQL to those who are just entering the world of the Oracle Database.

And starting with the Sept/Oct 2011 issue, we are including quiz questions within the article, which can also be answered at the PL/SQL Challenge site.

Of course, you don't need to read the article to take the quiz, but we hope you will do so.

One player has already submitted a question about this quiz, asking: "The Oracle Magazine quiz ends in 1613 days? Seriously?"

Yes, seriously. Each issue of Oracle Magazine has a "shelf life" of two-three months (before the next issue comes out), but the magazine is also available online. I know from experience that many Oracle technologists read these articles well into the future.

As a result, one way in which the Oracle Magazine PL/SQL quiz is different from others is that it will stay open for a really long time - until 31 December 2015.

But we will be selecting raffle winners randomly from all players, well before that, and likely more than once.

Enjoy!
Steven Feuerstein

Passing non-numeric strings to number arguments (4507)

2011-07-28T15:29:00.000+01:00

The 27 July quiz asked the following:

I create the following function in my schema:

CREATE OR REPLACE FUNCTION plch_num_diff (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RETURN CEIL (number_in) - FLOOR (number_in);
END;
/

Assuming that the value passed for number_in to this function is never NULL, which of the statements about this function are true?

A player wrote with the following objection: "I think this question is ambiguous. It is not clear about the scenario where a user passes a non-number value into the function."

I don't think this is a valid objection for this quiz because if you pass a string that cannot be implicitly converted into a number for the argument to plch_num_diff, the function will not be run. Instead, Oracle will raise a VALUE_ERROR exception when it attempts the conversion (and before the function itself is executed).

This can be demonstrated as follows: I try to call plch_num_diff with an argument value of 'abc'. Then I trap any exception and display the error message and backtrace.

DECLARE
   l_value   NUMBER;
BEGIN
   l_value := plch_num_diff ('abc');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_backtrace);
END;
/

Here's the output I see:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 4

Notice that the backtrace does not show an error raised inside the function. Contrast that to the output from the following statements. I now raise an exception insde plch_num_diff and pass it a valid numeric value.

CREATE OR REPLACE FUNCTION plch_num_diff (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RAISE VALUE_ERROR;
   RETURN CEIL (number_in) - FLOOR (number_in);
END;
/

DECLARE
   l_value   NUMBER;
BEGIN
   l_value := plch_num_diff (1);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_stack);
      DBMS_OUTPUT.put_line (sys.DBMS_UTILITY.format_error_backtrace);
END;
/

And now I see the following output, indicating that the error was raised within the function:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "HR.PLCH_NUM_DIFF", line 5
ORA-06512: at line 4

I therefore conclude that the behavior of PL/SQL in a block invoking plch_num_diff has nothing to do with the functionality of the function itself, which was the focus of the quiz.

Q2 2011 Championship Playoff Results

2011-07-27T19:37:00.001+01:00

You will find below the results of the Q2 2011 championship playoff. You can take a look at the quizzes that were in the competition through the Library page. These results will also be available through the Rankings page.

Congratulations to all players, of course, but let's give a special round of applause to our top three players:

Gary Myers (#1), who also ranked #2 in the last playoff and who wins a US$250 Amazon.com gift card. His first place ranking is especially impressive given the short time Gary took to answer the questions.
Chris Saxon (#2), who wins a US$175 Amazon.com gift card
João Barreto (#3), who wins a US$100 Amazon.com gift card

Good luck to everyone in the quizzes for Q3 2011 - I hope to see you in the next playoff!

Rank	Name	Country	Total Time	Total Score
1	Gary Myers	Australia	11 mins 21 secs	2848
2	Chris Saxon	United Kingdom	19 mins 32 secs	2734
3	João Barreto	Portugal	15 mins 06 secs	2598
4	Viacheslav Stepanov	Russia	19 mins 57 secs	2551
5	Frank Schrader	Germany	19 mins 50 secs	2353
6	Ludovic Szewczyk	Belgium	18 mins 35 secs	2328
7	Siim Kask	Estonia	18 mins 53 secs	2322
8	james su	Canada	16 mins 28 secs	2321
9	kowido	No Country Set	19 mins 49 secs	2254
10	Mike Pargeter	United Kingdom	16 mins 41 secs	2216
11	Sean Stuber	United States	20 mins 00 secs	2150
12	Randy Gettman	United States	18 mins 58 secs	2146
13	Filipe Silva	Portugal	18 mins 04 secs	2139
14	owbeg	Ukraine	15 mins 48 secs	2109
15	Dalibor Kova	Croatia	18 mins 37 secs	2053
16	Jeff Kemp	Australia	11 mins 38 secs	1942
17	macabre	Russia	19 mins 41 secs	1931
18	Theo Asma	Netherlands	14 mins 48 secs	1829
19	Mojibul Hoque	Bangladesh	03 mins 57 secs	1821
20	Sayan Malakshinov	Russia	04 mins 32 secs	1809
21	Kevan Gelling	Isle of Man	18 mins 28 secs	1781
22	Anna Onishchuk	Ireland	17 mins 12 secs	1706
23	DikkieDick	Netherlands	13 mins 58 secs	1696
24	Alexander Polivany	Ukraine	20 mins 00 secs	1675
25	mentzel.iudith	Israel	20 mins 00 secs	1575
26	Marc Thompson	Australia	19 mins 39 secs	1557
27	Q	Belgium	16 mins 12 secs	1551
28	clee	United States	17 mins 56 secs	1441
29	NickL	United Kingdom	19 mins 55 secs	1402
30	Pavel Zeman	Czech Republic	20 mins 01 secs	1375
31	Gerry171	United States	19 mins 00 secs	1345
32	Rajesh Venkataramani	India	16 mins 43 secs	1341
33	Rosemary	United States	18 mins 38 secs	727
34	Jerry Bull	United States	18 mins 47 secs	40

Objections to PL/SQL warnings quiz in Q2 2011 playoff (4483)

2011-07-24T15:00:00.000+01:00

The Q2 2011 playoff tested players' knowledge of Oracle's compile-time warnings, in particular some key warnings added in Oracle Database 11g Release 2. In this quiz, we showed a program unit and asked how many warnings would be displayed by Oracle.

Players had the following objections:

"I hope I never see another question like this again! I can't imagine how anyone could have gotten this one correct without actually typing in and running it. I would have preferred if the question was in the form "which of the following warnings would be shown", and the answers would include these and perhaps other unrelated warning messages."

"I think that this quiz was somehat unfair towards players that are using 11gR1. As by my test performed with 11gR1 (11.1.0.7.0.), there are only 4 warnings generated, namely: PLW-7203, PLW-5004, PLW-6009 and PLW-5005. The additional two ones, specific for 11gR2 are even not mentioned in the PL/SQL Documentation of 11gR2, nor in other sources, like your book PL/SQL Programming 5-th Ed, so those who are NOT using this version were unable to know about it. By the way, there is no consistent source that offers a complete list of all these warnings, for any version. So, I think that for the sake of fairness, this quiz should be rescored and considered as correct any answer that fits the previous releases, 11gR1 and 10gR2. I did not check 10gR2, it may be the same as 11gR1."

I can see why this quiz would have been a source of frustration and perhaps irritation, but I don't see why I should rescore.

The Oracle documentation of every version offers an Error Messages book, which contains (so far as I know) a comprehensive list of warnings. This quiz specified 11.2 as the minimum version.

So to answer the question correctly, you needed to (a) read the code and identify fairly obvious problems with it; (b) be generally conversant with the way compile-time warnings work; and (c) either be aware of the new warnings added in 11.1 and 11.2 or scan the documentation for verification of your analysis.

Valid partial step in optimizing code? (4482)

2011-07-22T21:36:00.000+01:00

In the Q2 2011 playoff, one of the questions examined the nuances of using BULK COLLECT and FORALL to optimize one's code. We asked players "Which of the statements below describe a complete or partial step towards improving the performance of this program, while keeping intact all current functionality?"

And we scored as incorrect this choice:

"Keep the cursor FOR loop in place (they are automatically optimized by Oracle into BULK COLLECT levels of performance). Simply replace the two DML statements with FORALL statements within that cursor FOR loop."

A player objected, feeling that this should be scored as correct, arguing:

"I checked this answer as correct because, as another player mentioned, the question did ask “complete or partial step towards improving the performance of this program”. It is not a solution, of course, because there needs to be a bulk collect off the cursor to create the collection(s) necessary for a forall statement to work. Of course, that would also be a “step”. But this answer also is a “partial step towards improving the performance”. I think that this answer should be scored as correct. I interpreted the word “step” to mean “one of things that I would need to do to improve the performance”. To make this answer incorrect, I would have added the following clarifying language to the question: “and resulting in a working program that compiles without error”. But the English language is much more ambiguous than the PL/SQL language."

Another player wrote to say about two other of the choices:

"I have to disagree with the answers to this question. The question clearly states "Which of the statements below describe a complete OR PARTIAL step towards improving the performance of this program, while keeping intact all current functionality?" (emphasis mine). Answers that describe a reasonable step that the programmer may take, even if they are not complete, should still be marked correct in my opinion. For example: "Replace the two DML statements (insert and update) with one FORALL statement that executes those same two statements, using data in collections populated by a BULK COLLECT." - you object to this answer because it doesn't completely fulfill the requirements. But a FORALL statement could most definitely be used to improve the performance - sure, it doesn't completely fulfill the requirements, the programmer has to do more than that, but it is still a partial step towards improving the program. Similarlarly, "Add a LOG ERRORS to both DML statements and put them inside FORALL statements." - this is a good partial step. All I have to do is add some code to raise an exception if I find an error in SQL%BULK_EXCEPTIONS. The other answers were steps in the *wrong* direction, and were correctly marked as wrong for that reason."

My responses:

First, I recognize the problematic aspect of including "or partial" in our question text. We actually included it so that a player couldn't argue that a single choice was or wasn't a complete solution. Ah, well...

Still, I am ready to defend with full vigor my scoring. Here goes:

It sounds like this rationale for marking this as correct is:

Sure, Steven, you say you will keep the cursor for loop "in place" but that's just for now. Later you will come back and change it to a bulk collect. So it is a partial step.

I certainly do understand that rationale. But I also find myself objecting to it. Why? Because the language of the choice implies (strongly, to my mind) that this is not simply a transitional step. It is a misunderstanding of the technology. Compare the text of answer 7281 to this:

Change the two DML statements into FORALL statements within the cursor FOR loop.

This actually still makes little sense (what collection would "feed" the FORALL statement?), but one could argue that the bulk collect step is "next." OK.

But in 7281, I explicitly say I will keep the cursor for loop "in place" and even include a parenthetical clause explaining why - this sentence is saying "This developer thinks that because the cursor FOR loop is auto-optimized, there is no need to change it. Then I include "Simply" in the second sentence to imply that I will do nothing more.

I realize that I am now having a discussion about English syntax and semantics, not PL/SQL code per se. But that is certainly the rationale for why I wrote it that way. I find it hard to read that choice and think to myself, "Oh, that is just the first step in a series planned by this person."

On to the others:

Replace the two DML statements (insert and update) with one FORALL statement that executes those same two statements, using data in collections populated by a BULK COLLECT. [7282]

I don't see how this could be interpreted as a partial/transitional step towards an improved program. You absolutely cannot replace these two DML statements with a single FORALL statement. Again, that reveals a misunderstanding of FORALL. You can only have one DML statement in each FORALL. So how can this be a partial step? You'd have to undo everything you do in this step to get it to work!

Add a LOG ERRORS to both DML statements and put them inside FORALL statements. [7286]

Again, I don't see how to recognize this as a valid step towards a properly refactored program. You cannot use LOG ERRORS without changing the behavior of the program. LOG ERRORS suppresses errors at the row level, and we need to do statement-level suppression of exceptions. You certainly can't add code using SQL%BULK_EXCEPTIONS, because when you use LOG ERRORS, no exceptions are raised at all. So that pseudo collection will remain empty.

Your thoughts?

Special $49.95 One-month Subscripton PL/SQL Channel

2011-07-21T17:32:00.002+01:00

I announced availability of the PL/SQL Channel several months ago: 27+ hours of recorded trainings on the Oracle PL/SQL language, available at the PL/SQL Channel and covering many (but admittedly not yet all) features of the PL/SQL language. At $395 per year per developer for unlimited access to all this content, the PL/SQL Channel is the most cost-effective use of your training dollars for PL/SQL developers, with much greater short- and long-term impact than one-time, instructor-led trainings.

I have also found, however, that many developers (or their managers) are reluctant to pay a subscription fee for a whole year, when their training budgets are very tight and they are not sure they will be using the PL/SQL Channel videos for such an extended length of time.

We have decided, therefore, to offer access to the PL/SQL Channel for 30 days for just $49.95. Yes, that's right - for less than $2 per day you can access dozens of PL/SQL videos with detailed trainings, plus their supporting presentation documents and code. This special price will be available through the end of September 2011.

We are also continuing our free, 30 day trial of the PL/SQL Channel (limited, however, to viewing of just five videos).

To start your free trial of the PL/SQL Channel, click here.

To order your $49.95 subscription to the PL/SQL Channel, click here.

Note: the thirty-day period starts on the day you purchase your subscription.

Tricky choice tricks the quiz author! (4720)

2011-07-18T13:59:00.000+01:00

In the logic puzzle for the week of 9 July, we scored the following choice as correct:

If 1 is in the solution then the other numbers must be (2, 4, 7).

The explanation given was this: "If 1 is in the solution, then the third turn tells us that (3, 6, 5) cannot be in the solution. The only 3 numbers left are (2, 4, 7) and looking over the first two turns, they would be compatible. So the four numbers (1, 2, 4, 7) could be in the solution together."

The third turn showed that (3 1 6 5) had just one correct digit, not in the right location.

As two players pointed out to me, the third turn shows that (2 4 7) have to be in the solution, regardless of the status of 1. We need four digits, the third turn tells us that at most one of (1 3 6 5) are in the solution, so we need all of the other three digits (2 4 7).

Thus, 2, 4 and 7 must be in the solution - but that fact has nothing to do with whether or not 1 is in the solution.

In other words, even if 1 is not in the solution, 2, 4 and 7 must still be in the solution.

So what's a poor quiz administrator to do about this? I was at first tempted to do what I usually do: give everyone credit for a correct answer to this choice, and change the text to get rid of this "problem."

But then I thought: wait a minute, the whole point of this puzzler is to test our facility with logical thinking. Clearly, I have demonstrated that I am not as good at this sort of thing as I thought (and by the way, 62% of all players selected this choice as correct).

So it seems to me that what I should do is (a) change this choice to be incorrect; (b) recalculate the scores of all players; (c) change the difficulty level of this quiz to Advanced.

What do you think, dear players?

Column headers "fair game" for choices? (4180)

2011-07-17T13:51:00.015+01:00

The SQL quiz on PIVOT for the week of 9 July asked which of the choices produce this desired output:

DEPARTMENT_ID       LESS       MORE
------------- ---------- ----------
          100          2          0
          200          2          1

And we scored a choice as incorrect simply because the column headers would not match what is shown above. Specifically, the headers would be:

DEPARTMENT_ID     'LESS'     'MORE'

A player raised a concern about this as follows:

I just answered today's SQL quiz a few seconds ago, and it looks to me, as far as I can remember, that something is missing from the general assumptions for the SQL quizzes, namely, a clear statement regarding the column headers. It would be very sad if those that perfectly master the PIVOT clause (including the alias usage for the column headers in this context), and have answered correctly today's quiz would be finally penalized because of eventually not accepting the quoted string as a correct column header, while the query result is the correct one, including the ordering. While from the previous quizzes we already learnt "silently" that the ORDER BY does matter, that is, a correct solution is supposed to produce the same ordering as presented in the sample shown in the quiz (ordering being always an intrinsic part of the SQL), the column headers issue is a little bit more "volatile", as, what we see it is not always strictly a part of the SQL statement itself ( for example, columns headings can be set by the client tool used, like the COLUMN statement in SQL*PLUS, a.s.o. ). In my opinion, the correctness of a query result set should not be made dependent on the column headers, though others might think otherwise. Regardless of the very specific case of the PIVOT clause, and maybe exactly because of it, and since our competition is still at the beginning, maybe a clarification on this issue in the assumptions would be most welcome and would remove ambiguities.

The author of the quiz offers this response:

We could perhaps make the assumptions a little bit clearer – but the assumptions should never become a multipage description that no one will read J.

I can understand a wish for the assumptions to make everything 110% unambiguous – but the danger is if we keep making the assumptions more and more detailed and larger and larger, then practically every tiny objection could argue that it should be part of the assumptions, and then the assumptions would become a document as large as the SQL documentation J. The more we add to assumptions, the less a player will feel that he/she has to use “common sense”…

The reason I included the “un-aliased” answer in this PIVOT quiz (and scored it false) was to enhance awareness of proper column naming. If for example this SQL statement were to be used in some client environment where the client code references columns of the resultset by name, then you would get errors if you didn’t alias. Example: IF (Resultset("LESS") > 1) {do something} would fail if the column was not aliased. So actually the correctness IMHO does depend on the headers as well as the correct data – if you are a “back-end” developer, how you name columns in your output will matter to the front-end developer who has to use your result sets in his or her code.

I would argue against rescoring this particular quiz – it was very much on purpose I provided a choice that failed only in the headers.

My (Steven) feeling about this: generally, we should not make choice correctness dependent on issues that are irrelevant to an understanding of the technology or how it plays out in our day to day experience. In this case, however, Kim argues persuasively that it was quite relevant and something for developers to be aware of.

What do you think?

Use LIMIT to "control PGA" in session? (4498)

2011-07-15T12:24:00.000+01:00

The 14 July quiz tested your knowledge of the LIMIT clause of BULK COLLECT. We scored as correct the following choice:

Use the LIMIT clause to control the amount of Process Global Area (PGA) memory used by the session running your program.

A player raised the following objection to this:

While limiting the number of rows returned is likely to reduce the amount of memory required, it does not constrain the amount of PGA allocated. Even if I were to agree that it controlled the amount of PGA used by the BULK COLLECT, that is not the same as controlling it for the session.

Before I respond, I would like to offer it up to other players for their comments. What do you think? Should we mark this as incorrect and why? Or do you feel this language is sufficiently clear to warrant a correct scoring?

The nuances of ending CASE (3577)

2011-07-08T16:48:00.000+01:00

The 7 July quiz tested your knowledge of using CASE expressions to implement conditional logic.

One player wrote: "I just marked all of the answers wrong because the CASE statements didn't end with END CASE (the SQL one I reckoned was wrong anyway). Is this an error in quiz, or is it deliberate, or am I wrong about CASE statements needing to end with 'END CASE'? I would have marked at least one answer as correct if it wasn't for that."

Unfortunately, this player was wrong, but the confusion is very understandable.

When you use CASE in PL/SQL, you can write either a CASE statement or a CASE expression.

A CASE statement requires an "END CASE" for termination.

A CASE expression is terminated only with an "END".

I don't really know why Oracle took these different approaches. Any thoughts out there?

TOO_MANY_ROWS and indeterminate behavior (3574)

2011-07-05T16:17:00.000+01:00

The 4 July quiz tested your knowledge of the impact of querying more than one row with a SELECT INTO. The TOO_MANY_ROWS exception is raised, but the question then is: what if anything will the variable(s) in the INTO clause of the SELECT statement be set to?

As we state in the answer:

According to Oracle documentation, the state of a variable that you SELECT INTO will be undefined if the statement raises TOO_MANY_ROWS....In practice, however, it seems that the data from the first row returned by the query is assigned to the variable(s) in the INTO clause; only when the second row is fetched is the TOO_MANY_ROWS exception raised.

We have had several quizzes that delve into such "indeterminate" behavior and generally have taken the position that if the actual, day-to-day experience does not seem to be indeterminate (in this case, the variable(s) are populated with the data from the first row fetched), then it is a legitimate topic and answer for a quiz.

Several players raised objections (of course!). One person objected to making a quiz involving undocumented behavior like this an "only one choice correct" quiz. That is too harsh a penalty for getting it "wrong" (which in this case would mean following what the documentation says). I agree with this. We are definitely going to change the question type to "more than one choice might be correct" and rescore. But we will likely also correct the scoring on some of the other choices.

Before I do that, though, I invite players who submitted concerns and code examples to post them on this blog. They've made some very interesting discoveries!

UNION ALL and sorting - first objection for a SQL quiz! (3466)

2011-07-05T13:48:00.000+01:00

The SQL quiz on GROUPING SETS (week of 25 June) raised the following objection from a player:

"I don't think the second choice (the "do-it-yourself implementation") is correct, because it relies on the fact, that the UNION ALL operator preserves the order of the rows returned by subqueries. This seems to be true in recent versions of Oracle database, but I cannot find it documented anywhere, so it should not be relied on. Or am I missing something here?"

Since I am fortunate enough to have authors and reviewers with much more depth in SQL than I working on the SQL quiz, I will post this objection and leave it to them (and the rest of you) to respond. Then we will conclude a next step for this quiz.

Q2 2011 PL/SQL Championship Playoff Participants

2011-07-04T16:18:00.001+01:00

We are pleased, indeed, to present to you the 44 players who have qualified to participate in the Q2 2011 PL/SQL Championship Playoff. For more details on how these players are selected, check out the FAQ. It takes lots of discipline and knowledge to make it to the playoffs, and we congratulate every single one of you.

We currently plan to hold the playoff on Thursday, 21 July, though that could change if enough players cannot participate on that day.

_Nikotin (Russia) Ranked 1 (Ranking)
Frank Schrader (Germany) Ranked 2 (Ranking)
kowido (No Country Set) Ranked 3 (Ranking)
Christopher Beck (United States) Ranked 4 (Ranking)
Joaquin Gonzalez (Spain) Ranked 5 (Ranking)
DikkieDick (Netherlands) Ranked 6 (Ranking)
Jeff Kemp (Australia) Ranked 7 (Ranking)
Pavel Zeman (Czech Republic) Ranked 8 (Ranking)
Siim Kask (Estonia) Ranked 9 (Ranking)
Dennis Klemme (Germany) Ranked 10 (Ranking)
Mojibul Hoque (Bangladesh) Ranked 11 (Ranking)
Jerry Bull (United States) Ranked 12 (Ranking)
mentzel.iudith (Israel) Ranked 13 (Ranking)
Filipe Silva (Portugal) Ranked 14 (Ranking)
Mike Pargeter (United Kingdom) Ranked 15 (Ranking)
Ludovic Szewczyk (Belgium) Ranked 16 (Ranking)
Sean Stuber (United States) Ranked 17 (Ranking)
Alexander Polivany (Ukraine) Ranked 18 (Ranking)
Viacheslav Stepanov (Russia) Ranked 19 (Ranking)
Hans Van Der Wildt (Belgium) Ranked 20 (Ranking)
Rajesh Venkataramani (India) Ranked 21 (Ranking)
Dalibor Kovač (Croatia) Ranked 22 (Ranking)
clee (United States) Ranked 23 (Ranking)
Randy Gettman (United States) Ranked 24 (Ranking)
Kevan Gelling (Isle of Man) Ranked 25 (Ranking)
Gary Myers (Australia) Ranked 30 (Wildcard)
sbramhe (No Country Set) Ranked 31 (Wildcard)
João Barreto (Portugal) Ranked 32 (Wildcard)
Chris Saxon (United Kingdom) Ranked 33 (Wildcard)
Anna Onishchuk (Ireland) Ranked 35 (Wildcard)
Theo Asma (Netherlands) Ranked 38 (Wildcard)
Q (Belgium) Ranked 42 (Wildcard)
xtender (Russia) Ranked 55 (Wildcard)
Marc Thompson (Australia) Ranked 58 (Correctness)
james su (Canada) Ranked 65 (Correctness)
owbeg (Ukraine) Ranked 69 (Correctness)
RajenB (Mauritius) Ranked 102 (Correctness)
Suresh Nair (United States) Ranked 122 (Wildcard)
macabre (Russia) Ranked 126 (Correctness)
Rosemary (United States) Ranked 173 (Correctness)
Arockia Arul Sekar (India) Ranked 246 (Correctness)
Tas Kartas (Australia) Ranked 261 (Correctness)
Gerry171 (United States) Ranked 397 (Correctness)
NickL (United Kingdom) Ranked 490 (Correctness)

PL/SQL Challenge

Compiling Incomplete Program Units (9633)

Q4 2011 Championship Playoff on 25 January

Exploring DBMS_UTILITY.FORMAT_CALL_STACK (9628)

Serializable Transaction Impact Not Seen by Players (9622)

Exploring Mutating Table Errors and FORALL (9619)

Fact Mining and the Weekly Logic Quiz (10983)

Hapy New Year and Participants in Q4 2011 Playoff

What is a "Generic" Oracle Error Message? (9614)

Thanks for a Great Year!

Are the Quizzes Too Long (Wordy)?

EXTEND and NOT NULL

%TYPE and RESULT_CACHE - Correction needed? (9604)

The Pain of a Missing WHERE Clause Predicate

The Complex World of Indexing (10193)

Looking for a few good...SQL quizzes

Late Report:: Results of Q3 2011 Championship Playoff

Nuances of Deterministic Functions in PL/SQL (9588)

Explanation for Slow Performance of VARCHAR2 Overloading?

How Reliable are PL/Scope Results? (9403)

Can Choice Be Correct If Error Raised? (9401)

Explorations into Pipelined Functions (9398)

"Average" function not correct due to lack of rounding? (9397)

Wanted: Your Ideas for Key PL/SQL Enhancements

No Error Log Table, So No Choices Correct? (7990)

Chinese Translations of PL/SQL Challenge Quizzes

Typo in Question Text Requires Score Adjustment (7988)

Empty String as Index Value in Collection (7987)

Start and End Dates of the Oracle Magazine Quizzes

Problems with Links to Oracle Documentation

Practice Makes Expert: New Feature Ready for Testing!

Participants in Q3 2011 Championship Playoff

Different parameters for UTL_FILE.FCOPY in 10g and 11g? (7982)

October SQL Quizzes by Tim Hall, ORACLE-BASE.com!

VALUES OF not a valuable and correct solution? (8304)

Recent Quizzes from the PL/SQL Challenge

Oracle PL/SQL Quizzes

SQL Quizzes

APEX Quizzes

What's the value of using %TYPE for parameters?

Players report disabled RESULT CACHE...why?

"Could" Changes Really Improve Performance? (7755)

New Poll on Changing Question Type

New Play a Quiz Features

IE8 and Compatibility View: TURN IT OFF!

PL/SQL Challenge Activity Summary for 5 September 2011 - 9 September 2011

Oracle PL/SQL Quizzes

SQL Quizzes

Oracle Application Express Quizzes

Mysteries of Implicit Conversions (7254)

SAVE EXCEPTIONS and LOG ERRORS: objections raised (7253)

Finding Quizzes in Play a Quiz

New weekly quiz on Toad for Oracle!

Daily PL/SQL Quizzes between 22 August and 26 August

Impact of NULLs and Analyzing Choice Correctness (5926)

PL/SQL Challenge upgrades to APEX 4.1

One, Two, Many Implicit Conversions (5986)

APEX Support in Toad

Daily Quiz Activity for 15 August 2011 - 19 August 2011

ALL_PROCEDURES Evolves Over Versions (5980)

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

Last week in the Oracle PL/SQL Challenge Daily Quiz

Wrong Scoring for Stateful Package Quiz? (5193)

FORALL, UPDATE and Reality-Doc Conflicts (6431)

Is typing and running code a form of cheating?

NLS Settings and Assumptions - Be Careful! (5187)

Public Profile Settings for Your PL/SQL Challenge Account

New Oracle Magazine Quiz Starts Today!

Passing non-numeric strings to number arguments (4507)

Q2 2011 Championship Playoff Results

Objections to PL/SQL warnings quiz in Q2 2011 playoff (4483)

Valid partial step in optimizing code? (4482)

Special $49.95 One-month Subscripton PL/SQL Channel

Tricky choice tricks the quiz author! (4720)

Column headers "fair game" for choices? (4180)

Use LIMIT to "control PGA" in session? (4498)

The nuances of ending CASE (3577)

TOO_MANY_ROWS and indeterminate behavior (3574)

UNION ALL and sorting - first objection for a SQL quiz! (3466)

Q2 2011 PL/SQL Championship Playoff Participants