oracle: timestamp ORA-01882: timezone region not found

 

>PROBLEM

Attempt to insert data using sqlplus (command console) into a table having TIMESTAMP type fails, returning error message.

SQL>INSERT INTO card( id, title, content, cardsz, colors, created_at, updated_at ) VALUES ( 1, 'test1 modified', 'hello world!\n', '', 'yellow', TIMESTAMP '2021-11-27 02:41:25 PM', TIMESTAMP '2022-06-01 09:56:56 PM');

- Output:

ERROR at line 1:

ORA-01882: timezone region not found

The target table configuration:

desc card;

ID NUMBER(10,0) No 1

TITLE VARCHAR2(255 BYTE) Yes 2

CONTENT CLOB Yes 3

CARDSZ VARCHAR2(16 BYTE) Yes 4

COLORS VARCHAR2(32 BYTE) Yes 5

CREATED_AT TIMESTAMP(6) No CURRENT_TIMESTAMP 6

UPDATED_AT TIMESTAMP(6) Yes "CURRENT_TIMESTAMP " 7

>SOLUTION

The issue was caused due a notation difference between the timestamp format migrated from a PostgreSQL dump, that uses the "AM|PM" and no reference to timezone (i.e. -03:00) values.

In the example given, the issue was solved just replacing the "PM" with the timezone value (-03:00) string from the timestamp, as follows:

INSERT INTO card( id, title, content, cardsz, colors, created_at, updated_at ) VALUES ( 1, 'test1 modified', 'hello world!\n', '', 'yellow', TIMESTAMP '2021-11-27 02:41:25 -03:00', TIMESTAMP '2022-06-01 09:56:56 -03:00');

>ENV

oracle 11g

sqlplus