Home » RDBMS Server » Server Utilities » SQL*Load - prevent carriage return char in last column?
icon5.gif  SQL*Load - prevent carriage return char in last column? [message #246624] Thu, 21 June 2007 08:56 Go to next message
stanjacko50
Messages: 6
Registered: June 2007
Location: London
Junior Member
Hi,

I have written a SQL*Load control file to import data from a tab delimited file, but the last field that is loaded is not ended with a tab. The last field gets loaded with an extra char (carriage return) as follows: last column of interface file (TRANSACTION TYPE NAME) contains text "New Business" yet what gets loaded into the table is "New Business|" because there is no comma at the end of the field - I would like to fix this without having to change the file generation program.

Control file:

===============================================

OPTIONS ( SKIP=1)
LOAD DATA
INFILE *

APPEND
INTO TABLE "LV_AR_BROKER_INTERFACE"
APPEND

FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS

(TRANSACTED_ID
,
TRANSACTED_DATE DATE "DD/MM/RRRR HH24:MI"
,
RATER_ID
,
TRANSACTION_TYPE_ID INTEGER
,
TRANSACTION_TYPE_NAME)

===============================================

Can anyone help please?
Thanks

[Updated on: Thu, 21 June 2007 09:10]

Report message to a moderator

Re: SQL*Load - prevent carriage return char in last column? [message #246635 is a reply to message #246624] Thu, 21 June 2007 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"records delimited by newline"

Regards
Michel
Re: SQL*Load - prevent carriage return char in last column? [message #246655 is a reply to message #246635] Thu, 21 June 2007 10:00 Go to previous messageGo to next message
stanjacko50
Messages: 6
Registered: June 2007
Location: London
Junior Member
Sound like that's the command I need, but I'm struggling to know where to put it - if I put it with the 'FIELDS TERMINATED BY' statement the load errors?

Thanks for the help so far!
Re: SQL*Load - prevent carriage return char in last column? [message #246676 is a reply to message #246655] Thu, 21 June 2007 11:16 Go to previous messageGo to next message
stanjacko50
Messages: 6
Registered: June 2007
Location: London
Junior Member
Solved my problem by adding the rtrim to the last field as you can see below - thanks for your help

===============================================

OPTIONS ( SKIP=1)
LOAD DATA
INFILE *

APPEND
INTO TABLE "LV_AR_BROKER_INTERFACE"
APPEND

FIELDS TERMINATED BY X'9'
TRAILING NULLCOLS

(TRANSACTED_ID
,
TRANSACTED_DATE DATE "DD/MM/RRRR HH24:MI"
,
RATER_ID
,
TRANSACTION_TYPE_ID INTEGER
,
TRANSACTION_TYPE_NAME "rtrim(:TRANSACTION_TYPE_NAME,chr(13))"
)

===============================================
Re: SQL*Load - prevent carriage return char in last column? [message #246698 is a reply to message #246676] Thu, 21 June 2007 11:39 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: Control File
Next Topic: ORA-00942: table or view does not exist
Goto Forum:
  


Current Time: Sat Dec 03 09:51:15 CST 2016

Total time taken to generate the page: 0.16100 seconds