External table, data read problem

[spicysudhi]

Hi

below is from my text file, saved in ANSI format.

700025,"Martin Bulíþ","."
700024,"CORECT Beránek s.r.o.","."

The database is NLS_CHARACTERSET = AL32UTF8 and NLS_NCHAR_CHARACTERSET = AL16UTF16

Below is extenal table script:

CREATE TABLE ALCM
(
ICCUST NUMBER(6),
ICCNAME VARCHAR2(90),
ICADD1 VARCHAR2(90)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY BPCSUN
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE LOG:'alcm.bad'
DISCARDFILE LOG:'alcm.dis'
LOGFILE LOG:'alcm.log'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
Missing field values are null
)
LOCATION (BPCSUN:'ALCM.DAT')
)
REJECT LIMIT UNLIMITED
LOGGING
NOCACHE
NOPARALLEL;


When I query the table, I get error message
"KUP-04021: field formatting error for field ICCNAME
KUP-04037: terminator not found
KUP-04101: record 1 rejected in file D:\ALCM.DAT

The first line is getting rejected due to the presence of the character "í" in it, which Oracle treats as line terminator!

How get around this?

Note: If I save the file as UTF8 (open in notepad and Save As with option UTF8) then there is no error.

regards,
Sudhi

 

[hoinz]

Hi Sudhi,

as there wasn't a helpful reply for a long time,
I'm going to write few lines, though I don't know if they will help much.
However it seems you answered your own question:
If I save the file as UTF8 (open in notepad and Save As with option UTF8) then there is no error.

If you don't like this answer, please keep in mind that there are a lot of character sets out there, and your database uses just one of them. Whenever you are going to transfer some data from an external source into your database, there may be the need of doing some kind of conversion. Actually Unicode was designed to overcome just these problems, but there is still a long way to go.
There are a few methods of doing such a conversion. One way, obviously, is to let the other application do all the work, that is, save the data in a format or in a character set that your database will understand without any further circumstances.
Another way is doing the conversion during loading the data to the database. As far as I know Oracle's sqlloader allows some kind of character set conversion. But I do not know if the same is true for an external table.

And by the way, notepad's formats ANSI and UTF8 are quite different regarding characters that are not 7-bit-ASCII.

regards

 

[spicysudhi]

thanks.
i wrote a small VB program to convert the file to UTF8 format and tht does the job for me now.

however, I tried many things with character set in oracle. i never able to come to one conclusion. may be i am not approaching it correctly.

regards,
sudhi