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;

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;
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:


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!


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:

$unset SQLPATH
SQL*Plus: Release - 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 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select sysdate from dual;
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?


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

------------------------------ ------------------------------

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!


Blogger Joel Garry said...

Weren't you happy with RR when Y2K came around? Aside from the bugs... (search for RR date format in the MOS archives for a trip down nightmare lane).

My brother had a breeding pair of Fawn Danes when I was a pre-teen, the humongous female especially knew she intimidated me. His kids rode them like horses. His friend with the pirate ship (really!) had a Harlequin with one blue eye. I assume you've seen the Doritos commercial? This one is a pretty amazing, I seem to recall they told me they knew Titan, whom I had seen around.

words: ndarem rthedee
words rocande iblityie

Wednesday, March 21, 2012 11:38:00 am  
Blogger Noons said...

Hehehe! That RR stuff was definitely one of the early debacles...

Thing I've learned with Tiago is how gentle GDs can be. This thing can't harm a fly, although his bark scares the heck out of me!
He's a funny pup. Only 9 months now, I shudder at how large he's gonna get once fully grown at 18 months!

Wednesday, March 21, 2012 9:33:00 pm  

Post a Comment

<< Home