Loading data from flat file using ORGANIZATION EXTERNAL [message #442686] |
Wed, 10 February 2010 00:05  |
osirus
Messages: 5 Registered: February 2010
|
Junior Member |
|
|
I have a flat file that contains french characters. when i am loading the file into my external table, it skips the line which contains the "é" character
Is there a way i can prevents that?
l_sql := 'create table idc_file_temp '||
'(line varchar2(4000)) '||
' ORGANIZATION EXTERNAL ( '||
' TYPE oracle_loader '||
' DEFAULT DIRECTORY GTECHFILES '||
' ACCESS PARAMETERS ( '||
' RECORDS DELIMITED BY NEWLINE '||
' BADFILE ''idc_file.bad'' '||
' DISCARDFILE ''idc_file.dis'' '||
' LOGFILE ''idc_file.log'' '||
' FIELDS TERMINATED BY X''0D'' RTRIM '||
' MISSING FIELD VALUES ARE NULL '||
' REJECT ROWS WITH ALL NULL FIELDS '||
' ( '||
' line char(4000) '||
' ) '||
' ) '||
' LOCATION ('''||l_invoice_file||''') '||
' ) '||
' PARALLEL '||
' REJECT LIMIT UNLIMITED ';
execute immediate l_sql;
|
|
|
|
Re: Loading data from flat file using ORGANIZATION EXTERNAL [message #442698 is a reply to message #442694] |
Wed, 10 February 2010 00:29   |
osirus
Messages: 5 Registered: February 2010
|
Junior Member |
|
|
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0
|
|
|
|
|
|
|
Re: Loading data from flat file using ORGANIZATION EXTERNAL [message #442707 is a reply to message #442706] |
Wed, 10 February 2010 01:26  |
osirus
Messages: 5 Registered: February 2010
|
Junior Member |
|
|
Yes michel i foud the answer to my problem. All i had to do was add the following CHARACTERSET WE8MSWIN1252 in my code.
Thanks a lot
l_sql := 'create table idc_file_temp '||
'(line varchar2(4000)) '||
' ORGANIZATION EXTERNAL ( '||
' TYPE oracle_loader '||
' DEFAULT DIRECTORY GTECHFILES '||
' ACCESS PARAMETERS ( '||
CHARACTERSET WE8MSWIN1252
' RECORDS DELIMITED BY NEWLINE '||
' BADFILE ''idc_file.bad'' '||
' DISCARDFILE ''idc_file.dis'' '||
' LOGFILE ''idc_file.log'' '||
' FIELDS TERMINATED BY X''0D'' RTRIM '||
' MISSING FIELD VALUES ARE NULL '||
' REJECT ROWS WITH ALL NULL FIELDS '||
' ( '||
' line char(4000) '||
' ) '||
' ) '||
' LOCATION ('''||l_invoice_file||''') '||
' ) '||
' PARALLEL '||
' REJECT LIMIT UNLIMITED ';
execute immediate l_sql;
[Updated on: Wed, 10 February 2010 01:38] Report message to a moderator
|
|
|