For one of my client i was having a requirement to pass the file name as parameter to the SQL loader program and capture the file name and the request id in the table.After surfing a lot on google and don't find anything so i come up with the following solution which works for me so thought of sharing the solution. Please find below the details.
1. Create Control file as below
2. In the control file we can use $FILE as the parameter to pass file name as parameter to the SQL Loader Program
LOAD DATA
INFILE '$FILE'
<APPEND/REPLACE/TRUNCATE>
INTO TABLE XX_STAGING_TBL
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
, MAIL_REQUEST_ID DECIMAL EXTERNAL NULLIF (MAIL_REQUEST_ID=BLANKS)
, ORDER_NUMBER DECIMAL EXTERNAL NULLIF (ORDER_NUMBER=BLANKS)
, ORDER_HEADER_ID DECIMAL EXTERNAL NULLIF (ORDER_HEADER_ID=BLANKS)
, LINE_NUMBER DECIMAL EXTERNAL NULLIF (LINE_NUMBER=BLANKS)
, ORDER_LINE_ID DECIMAL EXTERNAL NULLIF (ORDER_LINE_ID=BLANKS)
, MAIL_SEND_STATUS CHAR NULLIF (MAIL_SEND_STATUS=BLANKS)
, TO_RECIPIENT CHAR NULLIF (TO_RECIPIENT=BLANKS)
, CC_RECIPIENT CHAR NULLIF (CC_RECIPIENT=BLANKS)
, CREATION_DATE DATE "MM/DD/YY" NULLIF (CREATION_DATE=BLANKS)
, CREATED_BY DECIMAL EXTERNAL NULLIF (CREATED_BY=BLANKS)
, LAST_UPDATE_DATE DATE "MM/DD/YY" NULLIF (LAST_UPDATE_DATE=BLANKS)
, LAST_UPDATED_BY DECIMAL EXTERNAL NULLIF (LAST_UPDATED_BY=BLANKS)
, REQUEST_ID "STRING_TO_REPLACE"
)
3. Save the file and move it to the XXCUST_TOP/bin directoy.
4. After this write a shell script to execute the Control file as below
### Below command is used to replace the "STRING_TO_REPLACE" with the current request id so that i will insert the constant value in the table for column REQUEST_ID.
sed s/STRING_TO_REPLACE/$p_conc_request_id/ $XX_TOP/bin/XX_DEMO_CTL.ctl > $XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl
##go to the directory
cd $XX_TOP/bin
## run the SQLLDR for the New Control file
sqlldr ${p_userid_pwd} control=$XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl log=/usr/tmp/XX_DEMO_CTL.log bad=/usr/tmp/XX_DEMO_CTL.bad data=${p_file_name}
## After completion remove the newly created Control file and soft link.
rm ${XX_DEMO_CTL}_${p_conc_request_id}.ctl
exit
5. Define the HOST concurrent program for the above shell script and pass the file name as parameter.
6. submit the Concurrent program and look for the data in the REQUEST_ID column the request id of the Shell Script Concurrent Program should be populated.
7. Please test it before using it on PROD instance.
Note :- We can create new Control file at run time dynamically and remove it after processing the file. Or we can Update the same Control file to keep the value of request id column as constant and once the processing the csv file is complete again run the above mentioned sed command to replace the request id value to a string "STRING_TO_REPLACE"
1. Create Control file as below
2. In the control file we can use $FILE as the parameter to pass file name as parameter to the SQL Loader Program
LOAD DATA
INFILE '$FILE'
<APPEND/REPLACE/TRUNCATE>
INTO TABLE XX_STAGING_TBL
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
, MAIL_REQUEST_ID DECIMAL EXTERNAL NULLIF (MAIL_REQUEST_ID=BLANKS)
, ORDER_NUMBER DECIMAL EXTERNAL NULLIF (ORDER_NUMBER=BLANKS)
, ORDER_HEADER_ID DECIMAL EXTERNAL NULLIF (ORDER_HEADER_ID=BLANKS)
, LINE_NUMBER DECIMAL EXTERNAL NULLIF (LINE_NUMBER=BLANKS)
, ORDER_LINE_ID DECIMAL EXTERNAL NULLIF (ORDER_LINE_ID=BLANKS)
, MAIL_SEND_STATUS CHAR NULLIF (MAIL_SEND_STATUS=BLANKS)
, TO_RECIPIENT CHAR NULLIF (TO_RECIPIENT=BLANKS)
, CC_RECIPIENT CHAR NULLIF (CC_RECIPIENT=BLANKS)
, CREATION_DATE DATE "MM/DD/YY" NULLIF (CREATION_DATE=BLANKS)
, CREATED_BY DECIMAL EXTERNAL NULLIF (CREATED_BY=BLANKS)
, LAST_UPDATE_DATE DATE "MM/DD/YY" NULLIF (LAST_UPDATE_DATE=BLANKS)
, LAST_UPDATED_BY DECIMAL EXTERNAL NULLIF (LAST_UPDATED_BY=BLANKS)
, REQUEST_ID "STRING_TO_REPLACE"
)
3. Save the file and move it to the XXCUST_TOP/bin directoy.
4. After this write a shell script to execute the Control file as below
### Below command is used to replace the "STRING_TO_REPLACE" with the current request id so that i will insert the constant value in the table for column REQUEST_ID.
sed s/STRING_TO_REPLACE/$p_conc_request_id/ $XX_TOP/bin/XX_DEMO_CTL.ctl > $XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl
##go to the directory
cd $XX_TOP/bin
## run the SQLLDR for the New Control file
sqlldr ${p_userid_pwd} control=$XX_TOP/bin/${XX_DEMO_CTL}_${p_conc_request_id}.ctl log=/usr/tmp/XX_DEMO_CTL.log bad=/usr/tmp/XX_DEMO_CTL.bad data=${p_file_name}
## After completion remove the newly created Control file and soft link.
rm ${XX_DEMO_CTL}_${p_conc_request_id}.ctl
exit
5. Define the HOST concurrent program for the above shell script and pass the file name as parameter.
6. submit the Concurrent program and look for the data in the REQUEST_ID column the request id of the Shell Script Concurrent Program should be populated.
7. Please test it before using it on PROD instance.
Note :- We can create new Control file at run time dynamically and remove it after processing the file. Or we can Update the same Control file to keep the value of request id column as constant and once the processing the csv file is complete again run the above mentioned sed command to replace the request id value to a string "STRING_TO_REPLACE"