Today I used the official scripts (from https://github.com/oracle/db-sample-schemas)) to create sample schemas in Oracle database. In the log files I noticed the following error:
INSERT /*+ append */ INTO costs
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04020: found record longer than buffer size supported, 524288, in
C:\app\oracle\product\12.2.0\db-sample-schemas-12.2.0.1\sales_history\\sale1v3.d
at (offset=0)
As a result, the table SH.COSTS was left empty.
It was very easy to diagnose and fix the problem. The table SH.COSTS is populated from the external table SH.SALES_TRANSACTIONS_EXT, created by the following command:
CREATE TABLE sales_transactions_ext
...
ORGANIZATION external
...
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
...
LOCATION
('sale1v3.dat')
...
The source data file (sale1v3.dat) uses [LF] ('\n') as a record separator. And the Oracle loader driver ignores this separator, because my database is on Windows. On Windows "NEWLINE" separator is [CR][LF] ('\r\n'), which is not found in the file. This works on Linux/Unix, because "NEWLINE" separator there is exactly [LF] ('\n').
The fix was obvious - I changed the record delimiter in the definition of the external table SALES_TRANSACTIONS_EXT (in file "sales_history/lsh_v3.sql"), like this:
RECORDS DELIMITED BY '\n' CHARACTERSET US7ASCII
Then I restarted the scripts, the error disappeared and the table SH.COSTS was populated successfully. This definition will work both on Windows and on Linux/Unix.
Now, here is my (tough) question: Where should I address this finding, so that this silly bug can be fixed in the source? In this GitHub project there is file "CONTRIBUTING.md" which explicitly says "Due to widespread dependence on these scripts in their current form, no pull requests for changes can be accepted". And I doubt whether Oracle Support covers open source stuff like these scripts.