Forum Stats

  • 3,734,282 Users
  • 2,246,937 Discussions
  • 7,857,218 Comments

Discussions

Populating sample table SH.COSTS on Windows database

tonibony7
tonibony7 Member Posts: 14 Blue Ribbon
edited Nov 7, 2020 5:27PM in Database Installation

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.

Answers

  • User_4BKPW
    User_4BKPW Member Posts: 1

    I can't answer your question regarding where to address your finding, but I wanted to thank you for your detailed post regarding this issue and what you did to fix it. I encountered the exact same error today when trying to run the version 18 sample scripts on an Oracle 18c database running on Windows 10. I figured right away it was a Windows vs. Linux/Unix newline issue. I found your posting as a result of a Google search. I made your suggested change to the record delimiter in the lsh_v3.sql file and restarted the scripts. This time they ran with no errors. Thank you again for saving me a considerable amount of time.

    tonibony7
Sign In or Register to comment.