Symptom
In ODC, when a single-table CSV file is imported to an Oracle tenant, an error message is returned, indicating that the imported data and columns do not match.
Here is an example:
2021-12-08 10:11:36 [WARN] Retry Table: "T_3" Partition: 0 Records: 2 Error: ORA-01722: invalid number
2021-12-08 10:11:36 [ERROR] T_3 loaded failed, as the max errors: 1 is exceeded
2021-12-08 10:11:36 [ERROR] Error occurred when loading data into "CHZ"."T_3"
java.sql.SQLException: ORA-01722: invalid number
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1973) ~[oceanbase-client-2.2.6-SNAPSHOT.jar!/:?]
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1838) ~[oceanbase-client-2.2.6-SNAPSHOT.jar!/:?]
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1728) ~[oceanbase-client-2.2.6-SNAPSHOT.jar!/:?]
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:359) ~[oceanbase-client-2.2.6-SNAPSHOT.jar!/:?]
at com.oceanbase.jdbc.JDBC4PreparedStatement.executeInternal(JDBC4PreparedStatement.java:229) ~[oceanbase-client-2.2.6-SNAPSHOT.jar!/:?]
at com.oceanbase.jdbc.JDBC4PreparedStatement.execute(JDBC4PreparedStatement.java:158) ~[oceanbase-client-2.2.6-SNAPSHOT.jar!/:?]
at com.oceanbase.jdbc.JDBC4PreparedStatement.executeUpdate(JDBC4PreparedStatement.java:190) ~[oceanbase-client-2.2.6-SNAPSHOT.jar!/:?]
at com.oceanbase.tools.loaddump.utils.JdbcUtils.executeUpdate(JdbcUtils.java:296) ~[ob-loader-dumper-2.3.1-SNAPSHOT.jar!/:?]
at com.oceanbase.tools.loaddump.writer.database.DatabaseWriter$RetryCallback.call(DatabaseWriter.java:550) ~[ob-loader-dumper-2.3.1-SNAPSHOT.jar!/:?]
at com.oceanbase.tools.loaddump.writer.database.DatabaseWriter$RetryCallback.call(DatabaseWriter.java:438) ~[ob-loader-dumper-2.3.1-SNAPSHOT.jar!/:?]
at com.github.rholder.retry.AttemptTimeLimiters$NoAttemptTimeLimit.call(AttemptTimeLimiters.java:78) ~[guava-retrying-2.0.0.jar!/:?]
at com.github.rholder.retry.Retryer.call(Retryer.java:160) ~[guava-retrying-2.0.0.jar!/:?]
at com.oceanbase.tools.loaddump.writer.database.DatabaseWriter.onEvent(DatabaseWriter.java:308) [ob-loader-dumper-2.3.1-SNAPSHOT.jar!/:?]
at com.oceanbase.tools.loaddump.writer.database.DatabaseWriter.onEvent(DatabaseWriter.java:71) [ob-loader-dumper-2.3.1-SNAPSHOT.jar!/:?]
at com.lmax.disruptor.WorkProcessor.run(WorkProcessor.java:143) [disruptor-3.4.2.jar!/:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_275]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_275]
at java.lang.Thread.run(Thread.java:874) [?:1.8.0_275]
Possible causes
When the OBLOADER component in ODC is used to import data, OBLOADER sorts columns by column_id when listing columns. However, in some OceanBase Database versions, the sequence of the columns returned by select all_tab_cols in Oracle mode does not match the actual sequence. As a result, the insertion fails due to the error in column mappings during the import of the CSV file.
Affected versions
ODC V3.2.2 and earlier
Solutions
Upgrade ODC to V3.2.3 or later. This bug has been fixed in ODC V3.2.3.
If you use an ODC version earlier than V3.2.3, you can configure it to ignore incorrect column mappings.