Proposed Assumptions for National Language Settings (NLS)

Some players in the past have expressed concerns about the fact that the daily quiz assumptions do not currently specify English as the language. I have worked with Michael Brunstedt (well, to be honest, he did all the work), one of the quiz reviewers, to come up with the following text for assumptions regarding national language settings. I'd like to get your feedback before putting them into effect.

Tanks in advance for your comments and advice.

SF

Proposed Assumptions

The PL/SQL Challenge may reference various database generated messages or data values that are the result of database functional behavior. To establish a common basis for all players, the following default assumptions apply to all quizzes, but may be overridden within a quiz. First, these characters are supported in all Oracle character sets and are the only characters used for string values:
* Uppercase and lowercase English characters A through Z and a through z
* Arabic digits 0 through 9
* These punctuation marks: % ' ( ) * + - , . / \ : ; < > = ! _ & ~ { } | ^ ? $ # @ " [ ]
* These control characters: space, horizontal tab, vertical tab, form feed

The following calls to DBMS_SESSION.SET_NLS define national settings in effect for q 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');

Exploring nuances of 11g fine-grained dependencies (1841)

The 5 January quiz tested your knowledge or (or, more likely, introduced you to) the Oracle11g fine-grained dependency feature, which maintains dependency information down to columns within tables and parameters within subprograms.

The quiz demonstrates that if I add a column to a query that is executed in a stored program unit, then that the program unit is not invalidated, since that unit couldn't possibly have depended on the new, previously non-existent, column.

One player, Oleksandr, wrote with the following oddity: if your query includes reference to ROWNUM, then Oracle will invalidate the program unit after adding a column. You can see this fact below:

SQL: CREATE TABLE mumu
  2  (
  3     x   NUMBER
  4   , c   VARCHAR2 (5 BYTE)
  5   , z   NUMBER
  6  );

Table created.

SQL: INSERT INTO mumu (x, c)
  2       VALUES (2, 'x');

1 row created.

SQL: INSERT INTO mumu (x, c)
  2       VALUES (3, 'c');

1 row created.

SQL: COMMIT;

Commit complete.

SQL: CREATE OR REPLACE PROCEDURE mu
  2  AS
  3     xx   mumu.c%TYPE;
  4  BEGIN
  5     SELECT c
  6       INTO xx
  7       FROM mumu
  8      WHERE ROWNUM = 1;
  9  END;
 10  /

Procedure created.

SQL: SELECT status
  2    FROM user_objects
  3   WHERE object_name = 'MU';

STATUS
-------
VALID

SQL: ALTER TABLE mumu ADD y NUMBER;

Table altered.

SQL: SELECT status
  2    FROM user_objects
  3   WHERE object_name = 'MU';

STATUS
-------
INVALID

If I remove the reference to ROWNUM (change to "z = 1" for example), then the program unit is not invalidated.

I do not know why this happening. Does anyone "out there" have any ideas or any other comments about this quiz?

Cheers, SF

Many more reasons to play the PL/SQL Challenge in January!

We hope that you had a fine new year's celebration (assuming, that is, that you celebrate the new year on 1 January), took some time away from PL/SQL and the rest of your professional responsibilities, and re-charged those batteries.

That must have been what happened - because we saw the number of players slide downwards and Christmas and New Year's approached. But we forgive you and to make sure you "come back" to the PL/SQL Challenge, we are offering lots of raffle prizes this month - and you are eligible to win simply by playing (plus, the more you play, the greater your chances of winning).

• $100 Amazon.com gift card (sponsored by the PL/SQL Challenge)
• 2 $50 Amazon.com gift cards (sponsored by Enciva)
• 10 O'Reilly Media ebooks (sponsored by O'Reilly Media)
• 3 ODTUG memberships (sponsored by ODTUG)
• 10 signed bookplates from Steven Feuerstein (sponsored by...oh, you know who)
• A copy of the Malt Whisky Yearbook 2011 (sponsored by MaltMaps)

So be sure to play, and play each day, to improve your chances of winning one of these fine prizes.

Warm regards, best of luck in the new year!
Steven Feuerstein

Q4 2010 Playoff Participants

A new year, a new quarter - and that means it's time for championship playoff for the previous quarter, Q4 of 2010. So a very special "Happy New Year!" and congratulations to the following 45 PL/SQL Challenge players, who will soon be competing for top ranking and prizes.

The first 25 names listed qualified for participation through their ranking. The twenty players after that qualified either as a wildcard entry or through the "correctness" raffle. Check out the FAQ for details.

Dennis Klemme (Germany) ranked 1 with 66 quizzes in 3942 seconds 96.6% correct
pinkal soni (India) ranked 2 with 64 quizzes in 4453 seconds 96.1% correct
Eurico Matos (Portugal) ranked 3 with 64 quizzes in 5544 seconds 98.2% correct
V Vandana Patel (India) ranked 4 with 64 quizzes in 4102 seconds 94% correct
Frank Schrader (Germany) ranked 5 with 62 quizzes in 4111 seconds 98.5% correct
Elic (Belarus) ranked 6 with 65 quizzes in 7833 seconds 96.2% correct
Niels Hecker (Germany) ranked 7 with 65 quizzes in 6694 seconds 92.7% correct
Gunjan (India) ranked 8 with 61 quizzes in 3488 seconds 94.5% correct
Filipe Silva (Portugal) ranked 9 with 66 quizzes in 4385 seconds 89.4% correct
Peter Hraško (Slovakia) ranked 10 with 65 quizzes in 11059 seconds 96.2% correct
mentzel.iudith (Israel) ranked 11 with 66 quizzes in 8936 seconds 92.5% correct
Peter Schmidt (Germany) ranked 12 with 65 quizzes in 6710 seconds 89.9% correct
Rob van den Berg (Netherlands) ranked 13 with 66 quizzes in 12166 seconds 92.1% correct
Gary Myers (Australia) ranked 14 with 63 quizzes in 5344 seconds 89.6% correct
Henrikas Zukovskis (Lithuania) ranked 15 with 65 quizzes in 9889 seconds 93.4% correct
Mike Pargeter (United Kingdom) ranked 16 with 65 quizzes in 7210 seconds 89.9% correct
Pavel Zeman (Czech Republic) ranked 17 with 66 quizzes in 14817 seconds 96.6% correct
Yuriy Pedan (Ukraine) ranked 18 with 66 quizzes in 11179 seconds 93.2% correct
siamnobita (Thailand) ranked 19 with 64 quizzes in 52864 seconds 94% correct
Eigminas Dagys (Lithuania) ranked 20 with 66 quizzes in 9597 seconds 90.1% correct
Michael Meyers (United Kingdom) ranked 21 with 66 quizzes in 14686 seconds 94.9% correct
Jeff Kemp (Australia) ranked 22 with 65 quizzes in 8710 seconds 91.7% correct
Michal Cvan (Slovakia) ranked 23 with 65 quizzes in 14027 seconds 95.1% correct
Siim Kask (Estonia) ranked 24 with 64 quizzes in 12356 seconds 93.3% correct
Patrick Wolf (Austria) ranked 25 with 65 quizzes in 11257 seconds 92% correct
Markus Langlotz (Switzerland) ranked 28 with 66 quizzes in 9006 seconds 87% correct
João Barreto (Portugal) ranked 30 with 66 quizzes in 7357 seconds 84.9% correct
Urs Metzger (Germany) ranked 32 with 61 quizzes in 8120 seconds 89.5% correct
Theo Asma (Netherlands) ranked 34 with 63 quizzes in 9715 seconds 92.5% correct
Justin Cave (United States) ranked 38 with 61 quizzes in 8720 seconds 93.7% correct
Janis Baiza (Latvia) ranked 42 with 62 quizzes in 5789 seconds 86.4% correct
Chris Saxon (United Kingdom) ranked 44 with 65 quizzes in 6180 seconds 83.7% correct
Riccardo Butticè (Italy) ranked 46 with 64 quizzes in 8279 seconds 86.6% correct
Dominic Brooks (United Kingdom) ranked 49 with 62 quizzes in 15754 seconds 92% correct
Zoltán Pásztor (Hungary) ranked 57 with 65 quizzes in 9248 seconds 84.4% correct
Kim Berg Hansen (Denmark) ranked 62 with 61 quizzes in 9672 seconds 90.7% correct
Mariusz Kupczynski (Poland) ranked 157 with 51 quizzes in 4874 seconds 88.7% correct
Niels Jespersen (Denmark) ranked 169 with 66 quizzes in 20662 seconds 89.4% correct
Piet van Zon (Belgium) ranked 170 with 64 quizzes in 20705 seconds 91.9% correct
Steve Wika (United States) ranked 348 with 56 quizzes in 14824 seconds 89.5% correct
Binuraj Nair (United Kingdom) ranked 385 with 50 quizzes in 12480 seconds 91.3% correct
Boneist (United Kingdom) ranked 421 with 48 quizzes in 8347 seconds 90.7% correct
Uwe Küchler (Germany) ranked 843 with 38 quizzes in 14762 seconds 92.3% correct
SteliosVlasopoulos (Greece) ranked 1059 with 36 quizzes in 35066 seconds 89.5% correct
Aci Polajnar (Slovenia) ranked 1507 with 32 quizzes in 68588 seconds 89.7% correct

And here is the distribution of countries:

Germany: 6
United Kingdom: 6
Portugal: 3
India: 3
Slovakia: 2
Denmark: 2
Lithuania: 2
Netherlands: 2
Australia: 2
United States: 2
Estonia: 1
Austria: 1
Italy: 1
Greece: 1
Latvia: 1
Belgium: 1
Israel: 1
Czech Republic: 1
Switzerland: 1
Thailand: 1
Poland: 1
Belarus: 1
Ukraine: 1
Slovenia: 1
Hungary: 1

We will be finalizing the date for the playoff soon; more news to come!

Is "INTEGER" a reserved word? 30 December quiz (1824)

In the 30 December 2010 quiz on valid identifiers, we scored the following choice as correct:

DECLARE
   INTEGER   VARCHAR2 (100) := 'Everything''s Fine!';
BEGIN
   DBMS_OUTPUT.put_line (INTEGER);
END;

In our explanation, we wrote: "INTEGER" is a valid PL/SQL identifier and it is not a reserved word, so you can use it as the name of your variable. It is a datatype declared in the STANDARD package, one of the default packages of PL/SQL. You can, therefore, use that same identifier in your own code.

One player wrote the following:

"When reading your answer on the Quiz of Thursday 30 December 2010 I think it is not (completely) correct. 1) You're saying that INTEGER is 'not' a reserved word, but it is (select * from v$reserved_words). 2) I agree with the answer because it works, but I do not agree with your explanation, because why does the following not work? (on a 10.2 database.) -- This works: declare integer varchar2(100); begin null; end; -- This works to: declare varchar2 number; begin null; end; -- But together it does not work: --ORA-06550: line5, column 11: --PLS-00320: the declaration of the type of this expression is incomplete or malformed declare integer varchar2(100); varchar2 number; begin null; end; -- Also when giving the variable the same name as the (sub)type) does not work either; declare integer integer; begin null; end; declare number number; begin null; end; I hope you have an explanation for this strange behaviour."

I believe that I can provide an explanation.

First, INTEGER is a reserved word in SQL, but not in PL/SQL.

This means, for example, that I cannot create a table named INTEGER or a column in a table named INTEGER.

But in a PL/SQL block, I can name a variable INTEGER. That's because all of the base datatypes in PL/SQL are defined in the STANDARD package, one of two default packages of PL/SQL. The defining of a datatype like INTEGER in STANDARD means that:

1. I can declare a variable to be an integer in either of these two ways:

DECLARE
   l_int INTEGER;
   l_int2 STANDARD.INTEGER;
BEGIN
   NULL;
END;

2. "INTEGER" is "just" a type defined in a package. It is not a reserved word in the PL/SQL language itself. I can "override" the use of that name as a type for something else in my own code, like:

DECLARE
   INTEGER   VARCHAR2 (100) := 'Everything''s Fine!';
BEGIN
   DBMS_OUTPUT.put_line (INTEGER);
END;

When this block is compiled, PL/SQL resolves the reference to "INTEGER" as a variable of type VARCHAR2, and not as the STANDARD.INTEGER type.

As for the usages of seemingly reserved words that cause errors, consider the blocks below, each of which will cause compile errors:

DECLARE
   integer    VARCHAR2 (100);
   varchar2   NUMBER;
BEGIN
   NULL;
END;
/

DECLARE
   integer   INTEGER;
BEGIN
   NULL;
END;

DECLARE
   number   NUMBER;
BEGIN
   NULL;
END;
/

The reason Oracle complains about this code is that it cannot resolve the references to VARCHAR2, INTEGER and NUMBER, respectively in the three blocks. Sure, you can "override" VARCHAR2 in your own block of code, but then you have to use it in a way that is consistent with your override. If I use VARCHAR2 as a datatype, I cannot also use it as a variable name.

The most important thing to remember about the ability to "override" identifiers defined in STANDARD (and DBMS_STANDARD) is that YOU SHOULD NOT DO THIS. Oracle even has a warning defined for this:

PLW-05004: identifier string is also declared in STANDARD or is a SQL builtin

Cause: The indicated identifier was also either: o) declared in package STANDARD, or o) a SQL builtin function, or o) a pseudo-column. This situation can result in name resolution issues since the STANDARD/builtin declaration will be chosen over the local declaration in SQL statements; however the local declaration will be chosen over the STANDARD/builtin declaration outside SQL scope. (See the documentation on PL/SQL name resolution.)

Action: Either: o) rename the given identifier, o) qualify any references to the identifier with the containing scope's name, or o) make sure the intended resolution occurs if using the identifier in SQL scope.