Problem with FieldsAsString and dates with different formats.

MarkF · Post by **MarkF** » Fri 07 Mar 2008 15:47

Hi Folks!

It looks like there is a problem with FieldsAsString and the buffer size used to return dates when changes are made to NLS_DATE_FORMAT. Here's an example of what happens:

Code: Select all

SQL> select sysdate from dual
SYSDATE   
--------- 
07-MAR-08 
SQL> alter session set nls_date_format = 'MM-DD-YYYY'
SQL> select sysdate from dual
SYSDATE   
--------- 
03-07-200 
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'
SQL> select sysdate from dual
SYSDATE   
--------- 
07-MAR-20

Note that the size of the date string stays the same and truncates the string. Perhaps I'm missing a setting or call I can make to reset the buffer size? Thanks for any help or suggestions!

-Mark Ford
Benthic Software

Plash · Post by **Plash** » Tue 11 Mar 2008 09:30

ODAC reads the value of NLS_DATE_FORMAT one time at connecting or later. When you change NLS_DATE_FORMAT with ALTER SESSION, ODAC does not reread the value.

There is no special method to reset the settings stored in TOraSession component. But you can change the value of CharLength or Charset options to reset settings.

MarkF · Post by **MarkF** » Tue 11 Mar 2008 12:35

Hi Plash,

This means that I have to scan each statement and reset CharLength after any call that alters NLS_DATE_FORMAT. Will this be a problem in a multi-threaded environment where background threads are running queries of that session? I'm concerned that doing this will cause problems.

This is actually a pretty big deal since programs that would tend to use the FieldsAsString setting would also tend to use "alter session" to change the NLS_DATE_FORMAT (at least in my experience.)

Thanks for any help.

-Mark

Plash · Post by **Plash** » Thu 20 Mar 2008 08:02

To avoid the problem, DateFormat option of TOraSession should be used instead of ALTER SESSION.

MarkF · Post by **MarkF** » Thu 20 Mar 2008 12:23

Unfortunately that's not really an option in this case. The only real solution that I've found is to modify the source code to use a larger fixed buffer for the date string. Obviously this means a larger record buffer overall, but I don't seem to have any choice.

-Mark