Month: November 2010

Binding DBLINK to a Network Interface

Just thought I’d do a quick post on this one; came out of a conversation about a month or two ago. We had a single-instance database running on a failover cluster (RHCS). A database link existed for a related database and the connection had to pass through a firewall. The problem was the firewall: it […]

You can always learn something new.

It’s high time for this blog to come off hiatus.  I really don’t know why I let it go so long, just pre-occupied with work and extra curricular activities I guess.

One of those activities was to contribute two chapters to a new book from Apress, Pro Oracle SQL.  Though it was only two chapters, it did consume a significant amount of time.  Some folks seem to be able to bang out well written prose and code with seemingly little effort.  It seems that I labor over it more than most, at least it feels that way at times.

On to something new.  Not really new, but it was new to me the other day.  Or if it was not new, I had completely forgotten about it.

It has to do with the innocuous date formats used with to_date().  I ran into to some unexpected behavior from to_date() while running one of the scripts used for the aforementioned book.
When logging into a data base, part of my normal login includes setting the nls_date_format for my session:

 alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss'  

The main purpose of doing so is so that DBA scripts that include dates will display in my preferred format without the need to use to_date(to_char()) to display the preferred format while preserving the data type.

When writing scripts that may be used by others, or any circumstance where I cannot rely on a setting for nls_date_format, I will use to_char() and to_date() with format masks to ensure the script will run without error.

When developing scripts for use in published writing, I normally do not set nls_date_format for my sessions, but this time I had forgot to disable it.

So, when double checking the scripts to be included with the book chapters, I was rather surprised to see that one of them did not work.

 SQL> l  
1 select
2 to_date('01/01/2011 12:00:00','mm/dd/yyyy hh24:mi:ss')
3 , to_date('01/01/2011')
4* from dual;
, to_date('01/01/2011')
ERROR at line 3:
ORA-01843: not a valid month

The SQL session I was checking it from was connected to a  completely new and different database, setup just for the purpose of verifying that the scripts all worked as I expected, but one script failed on the to_date().  I at first thought it just do to not having a format mask specified in the second to_date(), but then immediately wondered why script had always worked previously. You can probably guess why, though at first I did not understand what was occurring.

The new environment was not setting nls_date_format upon login.  I had inadvertently setup my initial test environment where the scripts were developed with nls_date_format=’mm/dd/yyyy hh24:mi:ss’.

What surprised me was that to_date(‘01/01/2011’) had worked properly without a specific date format mask, and a date format that did not match the nls_date_format.

The “new” bit is that as long as the date format corresponds to part of the session nls_date_format setting, the conversion will work.

So, with nls_date_format set to ‘mm/dd/yyyy hh24:mi:ss’, we should expect to_date(‘01/01/2011’) to succeed.

This can easily be tested by setting a more restrictive nls_date_format, and then attempting to use to_date() without a format mask.

 SQL> alter session set nls_date_format = 'mm/dd/yyyy';  
Session altered.
SQL> select to_date('01/01/2011 12:00') from dual;
select to_date('01/01/2011 12:00') from dual
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

When I saw that error message, I then understood what was happening. to_date() could be used without a format mask, as long as the date corresponded to a portion of the nls_date_format.  When the specified date exceeded could be specified with nls_date_format, an ORA-1830 error would be raised.

In this sense it is much like number formats.  I was a little surprised that I didn’t already know this, or had forgotten it so completely.

But, here’s the real surprise.  The following to_date calls will also be correctly translated by nls_date_format.

 SQL> select to_date('Jan-01 2011') from dual;  
01/01/2011 00:00:00
1 row selected.

SQL> select to_date('Jan-01 2011 12:00:00') from dual;
01/01/2011 12:00:00
1 row selected.

This was quite unexpected it.  It also is  not new.  I tested it on various Oracle versions going back to, and it worked the same way on all.

There’s always something to learn when working with complex pieces of software such as Oracle, even something as seemingly simple as formatting dates.

Oracle Database 11g Interactive Quick Reference

Just a quick note about the Oracle Database 11g Interactive Quick Reference That was released today to the public. This Interactive Quick Reference, created by Oracle University and the Server Technology Curriculum group, is your Essential Guide to Oracle Database 11g Release 2. This interactive quick reference includes an architecture diagram, a comprehensive list of […]

Some Great New Features

I’ve been off exploring Oracle 11g for a little bit trying to figure out a strategy to upgrade my environment. Oracle 11g has a ton of new features, most of which are now "options" (in other words they cost more money).

When Oracle came out with compression at the segment level in 9i, I thought it was a great feature, at least in theory. But as I got to use compression, I found out that DDL

Do you have an Oracle Background?

In my recent 11gR2 Grid Infrastructure course, a discussion arose concerning an old chestnut of a topic: that of Oracle Database Instance background processes. This discussion arises quite often on courses and seminars due to the plethora of “background” processes added to the Oracle architecture since Oracle 7. Back in Oracle 7, there were four […]

TEL/電話+86 13764045638
QQ 47079569