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>
===============On TimesTen================
$ 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 timestamp
The 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>