Home » SQL & PL/SQL » SQL & PL/SQL » Loading data from flat file using ORGANIZATION EXTERNAL
Loading data from flat file using ORGANIZATION EXTERNAL [message #442686] Wed, 10 February 2010 00:05 Go to next message
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 #442694 is a reply to message #442686] Wed, 10 February 2010 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database character set?
NLS_LANG when you started the instance?
Length of line that is skipped?
Oracle version up to 4 decimals?

For the first 2 ones, query nls_database_parameters and nls_instance_parameters.

Regards
Michel

[Updated on: Wed, 10 February 2010 00:25]

Report message to a moderator

Re: Loading data from flat file using ORGANIZATION EXTERNAL [message #442698 is a reply to message #442694] Wed, 10 February 2010 00:29 Go to previous messageGo to next message
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 #442699 is a reply to message #442698] Wed, 10 February 2010 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is queried from what?
Please answer to ONLY what is asked but to ALL what is asked.

Regards
Michel

[Updated on: Wed, 10 February 2010 00:31]

Report message to a moderator

Re: Loading data from flat file using ORGANIZATION EXTERNAL [message #442700 is a reply to message #442698] Wed, 10 February 2010 00:35 Go to previous messageGo to next message
osirus
Messages: 5
Registered: February 2010
Junior Member
length of line is just 1
Basically wherever there is that "é' it skips that line and there are 3 occurences

oracle version is 10g release 2

i simply queried
select * from NLS_DATABASE_PARAMETERS;


[Updated on: Wed, 10 February 2010 00:40]

Report message to a moderator

Re: Loading data from flat file using ORGANIZATION EXTERNAL [message #442702 is a reply to message #442700] Wed, 10 February 2010 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
oracle version is 10g release 2

This is not 4 decimals but just 2.
I thought I asked pretty simple questions.

Also post
select * from user_external_tables where table_name='<your table>';

Regards
Michel
Re: Loading data from flat file using ORGANIZATION EXTERNAL [message #442706 is a reply to message #442702] Wed, 10 February 2010 01:14 Go to previous messageGo to next message
osirus
Messages: 5
Registered: February 2010
Junior Member
Oh yes no probs

Sorry the version is 10.2.0.1

well i am 2 month new to oracle pl/sql and i still learning Smile

as for your second query, its returning me an empty table
Re: Loading data from flat file using ORGANIZATION EXTERNAL [message #442707 is a reply to message #442706] Wed, 10 February 2010 01:26 Go to previous message
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

Previous Topic: an SQL query
Next Topic: matrix multiplication
Goto Forum:
  


Current Time: Sat Oct 01 02:30:39 CDT 2016

Total time taken to generate the page: 0.05335 seconds