Chacterset issue with SQL loader and external table

Just had an issue with different contents for tables loaded with sqlldr and external table for a same input data file.

The issue is mainly caused by the different default behavior on the characterset of these 2 methods.

For external table, if character set is not specified, oracle assumes the data file has the same character set with the database. The DB characterset can be found with the following query:

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

For sqlldr, if characterset is not specified, it will try to use the value found in your client side NLS_LANG. If NLS_LANG is not set, it uses the default value, which is AMERICAN_AMERICA.US7ASCII .