Re: Another interesting feature
Date: Fri, 23 Mar 2012 17:13:40 -0700 (PDT)
Message-ID: <16209096.1208.1332548020933.JavaMail.geo-discussion-forums_at_vbhy1>
On Friday, March 23, 2012 2:40:24 PM UTC-4, Mladen Gogala wrote:
> 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
An interesting note from the Tom Kyte at the HotSOS(.com) Symposium held in Dallas the week of Mar 5-9-2012. "Implicit conversions are beyond evil" Using NLS can cause implicit date conversions that will more than likely break what you thought the optimizer was supposed to do...
From his example:
ops$tkyte%ORA11GR2> create or replace procedure inj( p_date in date )
2 as
3 l_rec all_users%rowtype; 4 c sys_refcursor; 5 l_query long;
6 begin
7 l_query := ' 8 select * 9 from all_users 10 where created = ''' ||p_date ||''''; -- DOUBLE implicit conversion!
Translates to:
Where created = to_date( to_char( date-field ) );
While it may appear to work, it may not do what you expect and CAN introduce SQL injection. Bottom line: DON'T DO IT, but if you do, use the appropriate date with a date-picture and/or bind variables.
See his blog at:
http://tkyte.blogspot.com/2011/02/interesting-read-followup.html
So, while you CAN use a "feature" does not mean that you SHOULD use a feature.
:) Received on Fri Mar 23 2012 - 19:13:40 CDT