Time-type data exported from an Oracle tenant cannot be imported

Symptom

The following error is reported when you use ODC to import a time-type SQL data file that is exported from an Oracle tenant back to the Oracle tenant:

Reason: ORA-01843: not a valid month.

Possible causes

When OBDUMPER generates an INSERT statement, data of the DATE type is not converted by using the TO_DATE function. If the nls_date_format variable of the current session does not match data format of the DATE type in the generated INSERT statement, the insertion fails.

For example, if the nls_date_format variable of the current session is yyyy-mm-dd and the generated INSERT statement is INSERT INTO "ALLTYPE"("HIREDATE") VALUES ('2020-06-11 06:19:16');, the insertion fails.

You can use the following statement to check the value of the nls_date_format variable:

show variables like '%nls_date_format%';

Solutions

Solution 1: Modify session variables

Change the value of the nls_date_format variable to the data format of the DATE type in the INSERT statement. You can modify a session variable in two ways:

• Execute an SQL statement in the SQL console.

  set session nls_date_format='yyyy-mm-dd HH24:MI:SS';
  

• In ODC, choose Sessions > Session Properties, search for nls_date_format, and modify the value.

• 

Solution 2: Use the TO_DATE function to convert the data format

Modify the INSERT statement in the SQL script. Use the TO_DATE function to convert the data of the DATE type. Here is the sample code:

INSERT INTO "ALLTYPE"("HIREDATE") VALUES (TO_DATE('2058-04-21 06:19:16', 'yyyy-mm-dd HH24:MI:SS'));