NLS Settings and Assumptions - Be Careful! (5187)

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.