removing EOL delimiter from inserting into external table -oracle

Question

I have included notrim for rowdata column in external table as suggesterd by Alex (This is a continuation of this question,),

But now End of Line character is also appending at the rowdata column, I mean , End of line (CR-LF) is also joins at the end of rowdata.

I don't want to use substr() or translate() , since file size is around 1GB,

My external table creation process :

'CREATE TABLE ' || rec.ext_table_name || ' (ROW_DATA VARCHAR2(4000)) ORGANIZATION EXTERNAL ' ||
     '(TYPE ORACLE_LOADER DEFAULT DIRECTORY ' || rec.dir_name || ' ACCESS ' || 'PARAMETERS (RECORDS ' ||
     'DELIMITED by NEWLINE NOBADFILE NODISCARDFILE ' ||
     'FIELDS REJECT ROWS WITH ALL NULL FIELDS (ROW_DATA POSITION(1:4000) char)) LOCATION (' || l_quote ||
     'temp.txt' || l_quote || ')) REJECT LIMIT UNLIMITED'

Is there any other paramenter I can add , to remove the End-of-line character. Thanks.

EDIT 1:

My file :

Some first line with spaces at end
Some second line with spaces at end

My Ext table :

Some first line with spaces at end    <EOL>
Some second line with spaces at end   <EOL>

to be more clear , I will explain in java (when I assign column values to string , it is something like below),

without notrim :

rowdata[1]="Some first line with spaces at end";
rowdata[2]="Some second line with spaces at end";

with notrim:

rowdata[1]="Some first line with spaces at end    \n";
rowdata[2]="Some second line with spaces at end   \n";

what I want it to be :

rowdata[1]="Some first line with spaces at end    ";
rowdata[2]="Some second line with spaces at end   ";

the delimiter is also a part of rowdata, since no trim is specified.

EDIT2:

Line-Endings : CRLF

Platform :

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit

Production PL/SQL Release 12.1.0.1.0 - Production

"CORE 12.1.0.1.0 Production" TNS for Solaris: Version 12.1.0.1.0 -

Production NLSRTL Version 12.1.0.1.0 - Production

SELECT DUMP(ROW_DATA,1016) FROM EXT_TABLE WHERE ROWNUM = 1;

Typ=1 Len=616 CharacterSet=AL32UTF8: 41,30,30,30,30,30,30,30,30,30,30,31,30,30,30,30,37,36,36,36,44,30,30,30,30,31,32,35,30,38,31,36,32,35,30,38,31,36,31,33,34,37,30,39,44,42,20,41,30,36,31,30,30,30,30,30,30,30,30,30,30,30,30,32,30,30,4d,59,52,20,32,5a,20,30,31,36,30,30,30,31,32,31,32,33,34,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,52,49,42,46,50,58,30,30,30,31,30,30,30,30,30,30,30,30,31,30,36,32,38,30,31,30,32,30,30,47,20,20,20,20,53,20,20,30,30,30,30,30,30,30,30,30,30,30,20,20,20,20,20,20,20,4e,39,32,37,32,20,20,20,20,20,20,30,30,30,30,30,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,30,30,39,39,38,54,45,53,54,52,52,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,4f,50,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,54,52,41,4e,53,49,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,52,52,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,4f,50,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,54,45,53,54,54,52,41,4e,53,49,44,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,d

Len should be 615

Answer 1

Your file line endings are CRLF (suggesting the file is created in Windows?), but your database is running on Solaris. As the documentation says:

If DELIMITED BY NEWLINE is specified, then the actual value used is platform-specific. On UNIX platforms, NEWLINE is assumed to be "\n". On Windows operating systems, NEWLINE is assumed to be "\r\n".

As your database platform is Unix it's only using the LF (\n) as the record delimiter. You can either change the delimiter in your file, or change the terminated by clause to look for the Windows line-ending:

,,,
records delimited by "\r\n" nobadfile ...

If you might get files with either type of line ending and can't control that, you could add a preprocessor step to strip any that do exist. If you create an executable script file, either in the same directory as the file or (as Oracle recommends) in a different Oracle-accessible directory, say called remove_cr which contains:

/usr/bin/sed -e "s/\\r$//" $1

you can add a call to that in your external table definition, and keep the newline temrinator:

...
records delimited by newline nobadfile nodiscardfile
preprocessor 'remove_cr'
...

Make sure you read the the security warnings in the documentation though.

Demo with a temp.txt file with CRLF line endings:

create table t42_ext (
  row_data varchar2(4000)
)
organization external
(
  type oracle_loader default directory d42 access parameters
  (
    records delimited by newline nobadfile nodiscardfile
    preprocessor 'remove_cr'
    fields reject rows with all null fields
    (
      row_data position(1:4000) char notrim
    )
  )
  location ('temp.txt')
)
reject limit unlimited;

select '<'|| row_data ||'>' from t42_ext;

'<'||ROW_DATA||'>'                                                             
--------------------------------------------------------------------------------
<Line1sometext       >                                                          
<Line2sometext       >                                                          
<Line3sometext       >