Another interesting feature

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 23 Mar 2012 18:40:24 +0000 (UTC)
Message-ID: <jkig2o$lch$1_at_solani.org>

Here is another interesting feature that I have discovered after some experimenting:
NO WORKEE:

---

[mgogala_at_mladen ~]$ NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
[mgogala_at_mladen ~]$ sqlplus scott/tiger_at_apex41

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 23 14:31:38 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE

---

23-MAR-12 Elapsed: 00:00:00.02
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[mgogala_at_mladen ~]$

WORKS:

---

[mgogala_at_mladen ~]$ NLS_LANG="American"
[mgogala_at_mladen ~]$ sqlplus scott/tiger_at_apex41

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 23 14:36:31 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

Enter user-
[mgogala_at_mladen ~]$ NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
[mgogala_at_mladen ~]$ sqlplus scott/tiger_at_apex41

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 23 14:33:11 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE

---

2012-03-23 14:33:13

Elapsed: 00:00:00.01
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

In other words, if you need NLS_DATE_FORMAT shell variable to work from the shell, you MUST set NLS_LANG to a proper value. It doesn't always work:
[mgogala_at_mladen ~]$ NLS_LANG="Pig Latin"
[mgogala_at_mladen ~]$ sqlplus scott/tiger_at_apex41

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 23 14:36:31 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

Enter user-name:

BTW, I have "-a" set in the environment, whatever variable is defined, is automatically exported. I wonder when will Oracle develop NLS support for pig Latin?

-- 
http://mgogala.byethost5.com

Received on Fri Mar 23 2012 - 13:40:24 CDT