Did You Know #5 – SQL*Loader

Ever used SQL*Loader or temporary tables? Had a problem with file encoding?

When using SQL*Loader or create an external table, we are loading a text file into the database. The text file is encoded in specific characterset. When we load it into the database, we sometimes get flipped question marks (¿) or gibberish. This means that the file was not uploaded with the correct characterset. This can be fixed by specifying the correct characterset (CHARACTERSET string) in the control file of the SQL*Loader (CHARACTERSET string) or in the “create table” statement.
For SQL*Loader, if not specified, the default in the value of the NLS_LANG environment variable.
For external table the default is the database characterset.
Example for external table:

CREATE TABLE ext_table
  (id number(10),
   name char(20))
  ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
   DEFAULT DIRECTORY ext_dir
   ACCESS PARAMETERS
     (RECORDS
        CHARACTERSET UTF8
      FIELDS (id,name)
    LOCATION ('info.dat'));