jQuery in Action, 3rd edition
The moose likes Oracle/OAS and the fly likes What is the separator or It can be anything ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "What is the separator or It can be anything ?" Watch "What is the separator or It can be anything ?" New topic

What is the separator or It can be anything ?

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 388

I created a table as

create table TT(time timestamp(6));

Now I want to insert row in this table and I don`t want to use to_timestamp function with format_model to do this.
So I checked the timestamp_format from the view v$nls_parameters


insert into TT values('12-MAR-13');

It inserted one row because the pattern of character string '12-MAR-13' matches to that of DD-MON-RR HH.MI.SSXFF AM.

Now I tried some variation

insert into TT values('12-MAR-13 12:10:11.234567');

It worked , but I thought It will not work as I have used colon rather than period symbol to separate hour,minute and second.

insert into TT values('12-MAR-13 12-10-11.234567');

This also works ,again it works I thought It will not work for same reason (using hyphen in place of period).

insert into TT values('12:MAR:13 12-10-11.234567');

This also works.

insert into TT values('12-MAR-13 12:10:11:234567');

But this does not work.

Can you tell how and when variation is allowed and when you have to be dead right.


Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

In general, I'm not sure what your problem actually is. Why not to stick to the format that exactly matches the NLS settings? That one cannot fail (I'd say).

In particular, however, the decision to assume the NLS settings you happen to have just now is not a wise one. If the NLS settings changes for whatever reason, your script(s) stops to work. There are many many many questions on AskTom, for example, that all boil down to an assumption of a specific NLS settings that has subsequently changed.

At the very least, you should set the chosen NLS format for timestamp at the beginning of your script if you don't want to specify it explicitly everywhere. Using to_timestamp with explicit format is still the best solution, however, as maintaining SQL scripts that may change the NLS settings according to their needs can become difficult if you happen to have more of them and call one from another, for example.
I agree. Here's the link: http://aspose.com/file-tools
subject: What is the separator or It can be anything ?
It's not a secret anymore!