Character Replacement Upside Down Question Mark ¿ Using SQL Loader (SQLLDR) Example: Eastern European Character(s) ł Ł (Doc ID 2611980.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

There are many variations to the problem of loading data into a database using SQL*Loader ( SQLLDR ) or dbms_put which can result in a replacement character which does not represent the original physical character.
This problem can occur for any string / char / varchar data including addresses, names of products, description fields...basically any text field.
This problem is also generic to any data being transported into a database where a potential mismatch of characters exists between character sets.

In this case the problem was seen for a person's name, stored and then read from an application.

 Last_name , First_name

In this example we will use the following as the user name

First Name: ABC..

Last Name: Z....¿    <<

• The character was displayable in the original document.
• The source character set was not known.
• The exact mapping for the character (glyph or codepoint) was also not known.
• The application data with the problem character was saved into a flat file.
• The application flat file was moved to a location where a client inserted the text into a database using SQL LOADER.
• Once the data was viewed from the database or the same application the character was replaced with an upside down question mark.
• Several client settings were tried in order to attempt a successful loading of the data into the database but there was no systematic approach to identifying the problem factors.

select first_name,last_name from <some_table>;

FIRST_NAME LAST_NAME
---------- ---------------
ABC.....       Z....¿....

Changes

Loading text data from a new geographical region for the first time.
The character data from this new region included characters being loaded for the first time.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.