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
Notice
The modification takes effect on the current session. In shared session mode, the modification takes effect on all SQL windows. Therefore, proceed with caution.
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'));