SQL & PL/SQL

Hi,

I am trying to read an external text file with organization external table. However, my select query fails reading the file properly.

I use below create table -

create table TAB1 ( field_1 CHAR(10),field_2 CHAR(10) )

organization external (

type oracle_loader

default directory ABC_DL_DIR

access parameters (

 records delimited by newline

 fields (  field\_1 position(1: 4) char(10),

           field\_2 position(5:10) char(10) )   )

location ('0050-pcrx.txt.txt')

)

reject limit unlimited;

What I observe that, if I look at the file from Notepad++, the input file looks good with records showing in different line. However, if I open same file from Notepad, everything shows in single line. Can you please let me know how to deal with such input files. Since this file comes directly from a device, I don't have option to manipulate the file.

File seen from Notepad++ -

Same file seen from Notepad -

Select query error -

SQL> select * from DD;

select * from DD

*

ERROR at line 1:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-29400: data cartridge error

KUP-04018: partial record at end of file C:\TEMP\DATALOAD\0050-pcrx.txt.txt

Thanks & Regards,

-Anand