permaculture playing cards*
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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
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
Author

What is the separator or It can be anything ?

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 222

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

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM


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

It inserted one row because the pattern of character string '12-MAR-13 12.10.11.234567' 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.

Shukran


Oracle Java Programmer , Oracle SQL Expert
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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.
 
Consider Paul's rocket mass heater.
 
subject: What is the separator or It can be anything ?
 
Similar Threads
ojdbc14.jar to ojdbc6.jar Date Format Problem
Dealing with kanji character in oracle 11g with put_line function of utl_file in pl sql script
Passing array of Oracle colection objects from java to PL SQL
Exception in seam:Caused by: org.jboss.seam.RequiredException: In attribute requires non-null value
Search query is taking too long to search