Serializable Transaction Impact Not Seen by Players (9622)

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)

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)

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

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)

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!

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

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.