Hey Scott, where have you been ?

Today I missed Scott in my emp table. When selecting from EMP, Scott is not there. Gone…

Ok, let’s recreate the scott schema.

C:> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> spool scott.txt
SYS@lsc02> drop user scott cascade;
User dropped.
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:> sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
JAMES
FORD
MILLER

12 rows selected.

Hey Scott, I miss you!

Let’s see if I can dig out something in the log file I created with spool scott.txt

(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87',
'dd-mm-rr')-85,3000,NULL,20)
ERROR at line 2:
ORA-01858: a non-numeric character was found
where a numeric was expected

Hey! the UTLSAMPL script is not supposed to work where JUL is not a month ?
reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE /s /f NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ FRENCH_SWITZERLAND.WE8MSWIN1252

Since FRENCH has no month called JUL the UTLSAMPL failed to insert SCOTT. Let’s fix the issue :
C:>reg add HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
/v NLS_LANG /t reg_sz /d AMERICAN_AMERICA.WE8MSWIN1252
Value NLS_LANG exists, overwrite(Yes/No)? yes
The operation completed successfully.
C:>reg query HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1 /v NLS_LANG

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
NLS_LANG REG_SZ AMERICAN_AMERICA.WE8MSWIN1252

and retry

C:>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SYS@lsc02> @?/rdbms/admin/utlsampl
Disconnected from Oracle Database 10g Enterprise Edition

C:>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.3.0 - Production
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
SCOTT@lsc02> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

Welcome back Scott!

6 thoughts on “Hey Scott, where have you been ?”

  2. Chen Shapira

    Nice 🙂

    Did you notice the date mask? Scott’s hire date should change somewhere in the next 50 years…

    If you report this to metalink now, maybe it will be resolved before this becomes an issue.

  6. Asif Momen

    Good one!!!

    Alternatively, you can modify language settings at session-level instead of modifying it through regedit.

    SQL> drop table t purge;

    Table dropped.

    SQL> create table t as select * from emp where 1=2;

    Table created.

    SQL> insert into t values (7788,’SCOTT’,’ANALYST’,7566,to_date(’13-JUL-87′,
    2 ‘dd-mm-rr’)-85,3000,NULL,20)
    3 ;
    insert into t values (7788,’SCOTT’,’ANALYST’,7566,to_date(’13-JUL-87′,
    *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected

    SQL> alter session set nls_date_language=’AMERICAN’;

    Session altered.

    SQL> insert into t values (7788,’SCOTT’,’ANALYST’,7566,to_date(’13-JUL-87′,
    2 ‘dd-mm-rr’)-85,3000,NULL,20);

    1 row created.

    SQL>

Comments are closed.