Solution for error when from external tables when installing the SH sample schema

If you are trying to select from the external tables created in the SH sample schemas, and receive an error like this:

SQL>select count(*) from SALES_TRANSACTIONS_EXT;
select count(*) from SALES_TRANSACTIONS_EXT
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04001: error opening file $ORACLE_HOME/demo/schema/log/ext_1v3.log

It is most likely because you have used the operating system variable $ORACLE_HOME during installation, like this:

sqlplus /nolog
@mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd EXAMPLE TEMP $ORACLE_HOME/demo/schema/log/ mydb

This causes the directories created as a part of the installation to contain the variable rather than the physical path, and that will cause the directories to be non-working:

CREATE OR REPLACE DIRECTORY 
DATA_FILE_DIR AS '$ORACLE_HOME/demo/schema/sales_history';

CREATE OR REPLACE DIRECTORY 
LOG_FILE_DIR AS '$ORACLE_HOME/demo/schema/log';

Use an absolute path instead:

sqlplus /nolog
@mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd EXAMPLE TEMP /u01/oracle/18c/demo/schema/log/ mydb

And your directories will be created correctly:

CREATE OR REPLACE DIRECTORY 
DATA_FILE_DIR AS '/u01/oracle/18c/demo/schema/sales_history';

CREATE OR REPLACE DIRECTORY 
LOG_FILE_DIR AS '/u01/oracle/18c/demo/schema/log';

You should now be able to select from your external table:

SQL>connect sh/shpwd@mydb
Connected.
SQL>select count(*) from SALES_TRANSACTIONS_EXT;

  COUNT(*)
----------
    916039

The Oracle 18c installation of the samples schemas is documented here.
The Oracle 18c sample schemas on github