How to Pass the File name as Parameter and populate the request id of the concurrent Program dyanamically using SQL Loader

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"

Script To Generate SQL*Loader Control File (Doc ID 1019523.6)

SELECT    'LOAD DATA'
       || CHR (10)
       || 'INFILE '''
       || LOWER (table_name)
       || '.dat'''
       || CHR (10)
       || '<APPEND/REPLACE/TRUNCATE>'
       || CHR (10)
       || 'INTO TABLE '
       || table_name
       || CHR (10)
       || 'FIELDS TERMINATED BY '','''
       || CHR (10)
       || 'TRAILING NULLCOLS'
       || CHR (10)
       || '('
  FROM all_tables
 WHERE table_name = UPPER ('&1');

SELECT      DECODE (ROWNUM, 1, ' ', ' , ')
         || RPAD (column_name, 33, ' ')
         || DECODE (data_type
                  , 'VARCHAR2', 'CHAR NULLIF (' || column_name || '=BLANKS)'
                  , 'FLOAT', 'DECIMAL EXTERNAL NULLIF(' || column_name || '=BLANKS)'
                  , 'NUMBER', DECODE (data_precision
                                    , 0, 'INTEGER EXTERNAL NULLIF (' || column_name || '=BLANKS)'
                                    , DECODE (data_scale
                                            , 0, 'INTEGER EXTERNAL NULLIF (' || column_name || '=BLANKS)'
                                            , 'DECIMAL EXTERNAL NULLIF (' || column_name || '=BLANKS)'
                                             )
                                     )
                  , 'DATE', 'DATE "MM/DD/YY" NULLIF (' || column_name || '=BLANKS)'
                  , NULL
                   )
    FROM user_tab_columns
   WHERE table_name = UPPER ('&1')
ORDER BY column_id;

SELECT ')'
  FROM SYS.DUAL;