Skip to Main Content
Forums

SQL & PL/SQL

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQLLDR : using TRIM on COMMENT column

Vicky007May 14 2020 — edited May 15 2020

HI ,

I would like to know how we can apply trim function in control file columns ?

Here the case is a-bit different where column name is special reserved keyword COMMENT.

We can use reserve word by using double quotes around while doing select or even while loading. eg. "COMMENT"

But while applying trim function , it is not working , any suggestion on this ? may be Data Issue ? how to load it as it is getting loaded in MYSQL but not in ORacle 19c

OPTIONS (SKIP=1)
load data
APPEND
into table example
fields terminated by ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
A1,
"COMMENT" "TRIM(:\"COMMENT\")",
FILE_ID  CONSTANT 0,
)

Any suggestions how we can apply trim on COMMENT ?

We do not have option to rename the column in this case as it is fixed already in all system layers.

I found the record in file are having very different things and thus it is not getting loaded due to it. Check last column, any suggestion how we can load it ? 

A1,Comment 
1,Funds transferred from xxxxxxxxxxxxxxxxxxxxxxxxxx Account FT20134DB8VPB48                                                                                                                                                                                                                                                                                                                                        
2,Funds transferred from xxxxxxxxxxxxxxxxxxxxxxx Account FT20134CPLKKC12                                                                                                                                                                                                                                                                                                                                     
3,Funds transferred from xxxxxxxxxxxxxxxxxxxx Account FT201344KMFXC30                                                                                                                                                                                                                                                                                                                                       
4,Funds transferred from ssssssssssssssssssssss Account FT201340X0T7C13                                                                                                                                                                                                                                                                                                                                    
This post has been answered by odie_63 on May 14 2020
Jump to Answer

Comments

odie_63

What's the data type of this "COMMENT" column?

Vicky007

Comment varchar2(4000 char).

TRIM works but i think file has some white space ,new lines and etc ( i dont know much about it ) . i have copied file content for 5 records. Please check if we can have some option to load it via SQLLDR as we dont have much control on file creation

odie_63
Answer

See if the following modifications are sufficient :

OPTIONS (SKIP=1)
load data
APPEND
into table example
WHEN (A1 != BLANKS)
fields terminated by ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  A1,
  "COMMENT" char(4000) "TRIM(:\"COMMENT\")",
  FILE_ID  CONSTANT 0
)
Marked as Answer by Vicky007 · Sep 27 2020
1 person found this helpful
1 - 3

Post Details

Added on May 14 2020
#sql-pl-sql
3 comments
348 views