CODEWITHSUNDEEP
sqloracle-database
Potato
Potato

Reputation: 172

External table how to delete newline char from the end of each row

i have problem with loading rows from a file, the point is that when im using External table like this

create table table_name
(
  id VARCHAR2(60)
)
organization external
(
  type ORACLE_LOADER
  default directory DIRECTORY
  access parameters 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET EE8MSWIN1250 nobadfile nodiscardfile
    FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '\"' LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      ID VARCHAR2(60)
    )
  )
  location ('tmp.txt')
)
reject limit 0;

my all rows have the newLine byte at the end of row, only thing that works is after loading data from file is update all rows using this 

update table_name
set id = translate (id, 'x'||CHR(10)||CHR(13), 'x');

How can i make it automatically?

Upvotes: 2

Views: 1590

Answers (2)

Hijesh V L
Hijesh V L

Reputation: 269

while may have line delimiter as either \n or \r\n.. you can check that by opening file in notepad++ or any other supporting editor and by clicking show all characters

based no how the data is in the life you may create the external table as

 RECORDS DELIMITED BY '\r\n'  or
 RECORDS DELIMITED BY '\n'  etx

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21115

Check exactly what newline charcters are in your file and than define the record delimiter explicitely.

Example

 records delimited by '\r\n'

The probable cause of your problem is that the newline character is not compatible with your operating system - which topic you can address as well.

Upvotes: 1

Related Questions