Skip to Main Content
Forums

E-Business Suite

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to remove space after the last record

gkthomasJun 4 2019 — edited Feb 9 2021

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

Comments

jeffb1

What about a command to ensure column 1 of the input is not blank?

gkthomas

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

Vipul Patel

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

1 - 3

Post Details

Added on Jun 4 2019
#general-ebs-discussion
3 comments
78 views