3 Replies Latest reply on Jun 11, 2019 5:37 AM by Simple Patel

How to remove space after the last record

gkthomas Jun 4, 2019 3:41 PM

I am using a SQL Loader concurrent program to load a data from data file. I am using #EOR# string as an end of the record marker in order to handle CR and LF in the column data. All my data loading fine . However at the end of the file ( after the last record) there is a white space (CR or LF) . This space is going as a record and the SQL loader discard. As a result I get a warning in Concurrrent program . Even though I do not lose any data, this warning is annoying. How can I handle this in Oracle Control File? Please help. sample given below

column one Columntwo #EOR#

3434 customer 1 #EOR#

4544 customer 2 #EOR#

6565 customer 3 #EOR#

space.....space...space ....

George

1. Re: How to remove space after the last record

jeffb1 Jun 7, 2019 12:40 PM (in response to gkthomas)

What about a command to ensure column 1 of the input is not blank?
Like Show 0 Likes(0)

Actions
2. Re: How to remove space after the last record

gkthomas Jun 10, 2019 6:03 PM (in response to jeffb1)

of course I can use when clause on column to check whether its null or not. But when the "When Clause" fails , the oracle Oracle concurrent program give a warning . My goal is how to avoid the warning
George
Like Show 0 Likes(0)

Actions
3. Re: How to remove space after the last record

Simple Patel Jun 11, 2019 5:37 AM (in response to gkthomas)

Hello,

Use like below

OPTIONS (SKIP = 1)
LOAD DATA
APPEND
INTO TABLE xxxxx
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
ORGANIZATION_CODE CHAR "TRIM(:ORGANIZATION_CODE)",
LOCATOR_NAME CHAR "TRIM(:LOCATOR_NAME)",
DESCRIPTION CHAR "TRIM(:DESCRIPTION)",
INVENTORY_LOCATION_TYPE CHAR "TRIM(:INVENTORY_LOCATION_TYPE)",
SUBINVENTORY_CODE CHAR "TRIM(TRANSLATE (:SUBINVENTORY_CODE,'*' || CHR (13) || CHR (10),'*'))"
)

Thanks,
Simple
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.