Skip to Main Content

Database Software


4155996Feb 10 2020 — edited Feb 17 2020

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 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.


-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 - 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 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/;PermSize=40960;TempSize=256;OracleNetServiceName=<removed>;

(Default setting AutoCommit=1)


Command> create table test_timestamp ( col1 timestamp(6) );


Command> insert into test_timestamp values ('10-FEB-20 PM');

2813: Error converting from character string '10-FEB-20 PM' to Oracle timestamp

The command failed.


Command> insert into test_timestamp (col1) values ('2020-02-10 12:46:48.802050000');

1 row inserted.


Command> commit;



Post Details
Added on Feb 10 2020
1 comment