Carrying filename and request_id into table when doing sqlldr

Sometimes when we sqlldr data file into table, we may need to carry filename and request_id into each line. sqlldr doesn't seem to be able to pass env variables into table, so what I can do is to massage the data file by appending file name and request_id into each line
#cat test.txt
1,2,3,4
4,3,2,1

#export v_request_id=20232123
#export v_file_name=test.txt

#cat test.txt | awk -F,  '{printf("%s/n","'$v_request_id','$v_file_name',"$0)}' > text_new.txt
The control file is like following:
LOAD data
APPEND
INTO TABLE MY_TEST
FIELDS TERMINATED BY ','  optionally enclosed by '"'
TRAILING NULLCOLS
( COL1              "LTRIM(RTRIM(:COL1))"
 ,COL2              "LTRIM(RTRIM(:COL2))"
 ,COL3              "LTRIM(RTRIM(:COL3))"
 ,COL4              "LTRIM(RTRIM(:COL4))"
 ,REQ_ID            "LTRIM(RTRIM(:REQ_ID))"
 ,FILE_NAME         "LTRIM(RTRIM(:FILE_NAME))"
)

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值