NLS_TIMESTAMP_FORMAT in Timesten

4155996

Hi there,

Please check the below logs from "Oracle DB" and from "TimesTen DB".

-We are basically trying to insert a timestamp value as a string from a Pro*C program.

-There is no explicit conversion to a specified format in the application as we are setting the NLS_TIMESTAMP_FORMAT in the session as 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT'. It is bad practice, but we have inherited this legacy code.

-While "insert into test_timestamp values ('10-FEB-20 12.46.48.802050 PM');" works in Oracle, it fails in Timesten. We are assuming it is because of different NLS_TIMESTAMP_FORMAT in TimesTen database.

-To prove, it is because of different NLS_TIMESTAMP_FORMAT, we can see that "insert into test_timestamp (col1) values ('2020-02-10 12:46:48.802050000');" works in TimesTen.

Query:

-Is there an easier way handle this incompatibility? We would ideally want to not change the app code to reformat the string to match TimesTen NLS_TIMESTAMP_FORMAT.

-What we understand is that we cannot ALTER SESSION in TimesTen to change the NLS_TIMESTAMP_FORMAT. So it is currently forcing to reformat the string in our application in million places to make the piece of code work with TimesTen.

-Is there a way to set NLS_TIMESTAMP_FORMAT globally in the TimesTen?

===============On Oracle================

 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters and Automatic Storage Management options  SQL> create table test_timestamp ( col1 timestamp(6) );  Table created.  SQL> insert into test_timestamp values ('10-FEB-20 12.46.48.802050 PM');  1 row created.  SQL> commit;  Commit complete.  SQL> select value from nls_database_parameters where parameter = 'NLS_TIMESTAMP_FORMAT';  VALUE ---------------------------------------------------------------- DD-MON-RR HH.MI.SSXFF AM  SQL>

$ ttisql "uid=<removed>;pwd=<removed>;dsn=sampledb"Copyright (c) 1996, 2019, Oracle and/or its affiliates. All rights reserved.Type ? or "help" for help, type "exit" to quit ttIsql.connect "uid=<removed>;pwd=********;dsn=<removed>";Connection successful: DSN=<removed>;UID=<removed>;DataStore=<removed>;DatabaseCharacterSet=WE8ISO8859P1;ConnectionCharacterSet=WE8ISO8859P1;DRIVER=<removed>/install/lib/libtten.so;PermSize=40960;TempSize=256;OracleNetServiceName=<removed>;(Default setting AutoCommit=1)Command>Command> create table test_timestamp ( col1 timestamp(6) );Command>Command> insert into test_timestamp values ('10-FEB-20 12.46.48.802050 PM'); 2813: Error converting from character string '10-FEB-20 12.46.48.802050 PM' to Oracle timestampThe command failed.Command>Command> insert into test_timestamp (col1) values ('2020-02-10 12:46:48.802050000');1 row inserted.Command>Command> commit;Command>Command><code>===============On TimesTen================<span style="font-size: 8pt;"><br/></span>

Gaz in Oz

One of the bigger issues here is the use of 2 digit years! Surely that warrants editing the App code to fix...

WRT to your posted issue, change the NLS_TIMESTAMP_FORMAT for Oracle inserts to match Timesten TIMESTAMP format

'yyyy-mm-dd hh24:mi:ss.ffffff'

and change the format of the timestamp strings to that format, it will then work on both db's.

For your example, format the timestamp data as

'2020-02-10 12:46:48.802050'