Load EBCDIC character with code higher than 127 into Oracle using SQLLoader
HaedronJun 23 2008 — edited Jun 24 2008Fellows,
I have a very complex problem.
I have to load a EBCDIC (from IMS database) file into Oracle using SQL*Loader.
It's easy to achieve this when I do set the Loader script with right parameters like the following code:
"OPTIONS(SKIP=0, ROWS=10000, ERRORS=100000)
LOAD DATA
CHARACTERSET "WE8EBCDIC37"
INFILE "LOHIVALEBC.DAT" "FIX 28"
BADFILE "LOHIVALEBC.BAD"
DISCARDFILE "LOHIVALEBC.DIS"
append
PRESERVE BLANKS
INTO TABLE "TEST_LOADER"
etc..."
This script runs very well. But it does not work well when I generate the file with characters in which code is above 127.
For these characters, Oracle converts any character code above 127 to 191. I think the reason is because the Oracle database language is set to WE8ISO8859P1 and Oracle does not find the match in the WE8ISO8859P1 codepage, so it returns the default value 191.
I'd tried to change the Oracle database language to UTF-8, but the problem got worst. Every character with code above 127 became in a 2 byte character, the column size needed became the double.
I do need to receive the correct character code because we are migrating a IMS Database to Oracle Database and there are low-values and high-values that must be converted in special characters code like 255. Low-value and high-value do not exist in Oracle, so we are trying to replace in file any ocurrence of low or high-value by character 255. The main idea is to generate a extracted file from IMS with every occurrence of low or high-value replaced by special characters like 255. The SQL Loader will read this extracted file and will write these characters in the table. But instead of be able to read 255 and to write 255, Oracle is reading 255 and writing 191. The same behaviour occurs for any character code above 127.
The only hope and workaround we are thinking is to generate the file in ASCII instead EBCDIC, but this adds one more step into process to convert EBCDIC to ASCII and we want to avoid this kind of step.
Anyone had faced the same or similar problem?
Best regards
Marcos Takashi Fukaya