just some basic stuff

...that's been bugging me for a while, about time I blog about it.

This post is mostly for my own reference.

Although of course it might be useful for the odd budding dba out there who still believes the command line is a useful weapon in their armory.
Yeah, they do exist!

I'm one of them, thank you.

So, have you been using sqlplus of late? And been bugged by the default Oracle data format we all hate since R3?
Yes, that one: DD-MON-RR.
You know, good ole:


SQL> select sysdate from dual;
SYSDATE
---------
19-MAR-12


Annoying, isn't it? Particularly when you have to check the time, or sort things by date. It's not easy to read, and I'll leave it at that!

Of course, one can do:


to_char(sysdate,'YYYY-Mon-DD HH24:MI:SS')


whenever one selects SYSDATE, or any other date column. And get back a nicely formatted timestamp that actually means something in an ordered list:


SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')
------------------------------------------------------------
19-MAR-2012 14:50:14


But I'm lazy and I hate typing too much. Besides, it's only too easy to mis-type some of that.

So, what are the options available for those of us who insist on using the command line tools?

First, we can always set a default, database-wide parameter - NLS_DATE_FORMAT - to the format mask above.

Something like:

NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

Be that via spfile or init.ora.

And we'll promptly break ALL applications we may be running that rely on the 25 year old default date format!


Needless to say, there might be a few gentler ways of achieving this!...

Here is one - for good old SQL*Plus:

We can at login time alter our session - and ONLY our SQL*Plus session - to set the NLS_DATE_FORMAT parameter to an adequate format string.

sqlplus will execute at login time any commands it finds in the $ORACLE_HOME/sqlplus/admin/glogin.sql file.


But just like before: this is a global SQL*Plus setting, so ALL sessions - ours and ANY other batch or interactive SQL*Plus sessions will inherit this setting!


Fortunately, there is a way around that. SQL*plus can also look for a "local" login.sql file. Note that this one doesn't have the "g"-leading name!

But in order for it to recognize that file, we need to set an environment variable that tells it where to find our default *.sql files. It's called SQLPATH.

So, if we set for example


export SQL_PATH=$HOME/sql;.


then sqlplus will look at login time for a file called "login.sql" in either our $HOME/sql directory (where we stash all our home-brew scripts, right?) or in the current directory - the ";." bit in the command above.

If we then add a suitable alter session to that login.sql file, the default date format will change only for sqlplus client users.

Very useful. And sometimes it can still get us into strife!

How?

Well, that thing will apply to only ALL sessions of SQL*Plus executing under that login. Be they us, or a batch sql cron job, or something else running that command line program. And only to SQL*Plus!
If we use any other interactive client command line product, it won't recognize this change in default date format.

Ideally then we need something that can be recognized by all client tools we may be running from the command line, not just SQL*Plus.

It turns out there is such an animal. Yes, it is another environment variable. Or rather: two environment variables to be set.

Here they are - for my system:


$export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
$export NLS_LANG=AMERICAN
$unset SQLPATH
$sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Mar 19 17:19:04 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select sysdate from dual;
SYSDATE
--------------------
19-MAR-2012 17:19:12


These will apply to all client sessions - SQL*Plus or otherwise - started in that terminal and will not affect any other logins.

The interesting thing is that the manual where this is mentioned seems to imply that only NLS_DATE_FORMAT is needed. In fact you need both variables, otherwise the date format will be ignored!

So, how do you find quickly what NLS_LANG value to use?

Look it up in DATABASE_PROPERTIES:


1 select property_name,property_value
2 from database_properties
3* where property_name = 'NLS_LANGUAGE'
SQL> /

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN



And that's about it for now.


Guess who joined our family ranks recently:


That's Tiago(Diego) on the left: our new great dane, all 60Kgs(!)of it. Ziggy - our old standard schnauzzer - is on the right, getting very annoyed!
I call this one:
"you toucha my buddy, I breaka ya face!"
but you need to be from Australia to understand it! ¦D

Catchyalata, folks!