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))"
)