External table load failing

[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 scrupt:


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?

regards,
Sudhi

[waitecj]

Do you have NLS_LANGUAGE\TERRITORY set correctly for your location?

[spicysudhi]

yes
it is set to UNITED KINGDOM

[Axr2]

Seems to work ok for me..atleast doesn't error out.

SQL> CREATE TABLE ALCM
2 (
3 ICCUST NUMBER(6),
4 ICCNAME VARCHAR2(90),
5 ICADD1 VARCHAR2(90)
6 )
7 ORGANIZATION EXTERNAL
8 ( TYPE ORACLE_LOADER
9 DEFAULT DIRECTORY ADMIN_DAT_DIR
10 ACCESS PARAMETERS
11 (
12 RECORDS DELIMITED BY NEWLINE
13 BADFILE admin_bad_dir:'blah.bad'
14 DISCARDFILE admin_log_dir:'blah.dis'
15 LOGFILE admin_log_dir:'blah.log'
16 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
17 Missing field values are null
18 )
19 LOCATION ('blah.txt')
20 )
21 ;

Table created.

SQL> select * from alcm;

ICCUST
----------
ICCNAME
--------------------------------------------------------------------------------

ICADD1
--------------------------------------------------------------------------------

700025
Martin Bul¥õ¡á
.

700024
CORECT Ber©¬nek s.r.o.
.

SQL>

[spicysudhi]

ur file saved in ANSI format?? and database charsets are same as mine?