0 Replies Latest reply on Jan 21, 2020 2:22 PM by Peter de Vaal

    Exporting data with timestamp columns as insert statements might give errors

    Peter de Vaal

      Exporting data as insert scripts from a table containing timestamp columns generates statements with a to_timestamp(timestamp literal, timestamp format) expression.

      SQL developer generates e.g. the following:

       

      to_timestamp('20-01-20 21:55:38,305108000','DD-MM-RR HH24:MI:SSXFF')

       

      The radix character (separator between seconds and fractional seconds) is a comma because my NLS_TERRITORY setting in SQL Dev is e.g. 'FRANCE'. If I had 'AMERICA' it would be a period.

      The format mask, on the other hand, has the 'X' for the radix character, which would take the local setting. However, it ignores the local setting and seems to use the database setting. If that is a territory using the period then the insert will fail.

       

      So it is important to know what the database setting is before doing the export. This could easily be avoided by not using the 'X' in the format mask, but in both the literal and the format mask using a hardcoded radix character.

      Could this be considered for the next version?