Database Software

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!

How to store data file name in one of the columns of staging table

User_ADHUEJun 13 2012 — edited Jun 13 2012

My requirement is to load data from .dat file to oracle staging table. I have done following steps:

1. Created control file and stored in bin directory.
2. Created data file and stored in bin directory.
3. Registered a concurrent program with execution method as SQL*Loader.
4. Added the concurrent program to request group.

I am passing the file name as a parameter to concurrent program. When I am running the program, the data is getting loaded to the staging table correctly.

Now I want to store the filename (which is passed as a parameter) in one of the columns of staging table. I tried different ways found through Google, but none of them worked. I am using the below control file:

OPTIONS (SKIP = 1)
LOAD DATA
INFILE '&1'
APPEND INTO TABLE XXCISCO_SO_INTF_STG_TB
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COUNTRY_NAME
,COUNTRY_CODE
,ORDER_CATEGORY
,ORDER_NUMBER
....
....
....
....
,RECORD_ID "XXCISCO_SO_INTF_STG_TB_S.NEXTVAL"
,FILE_NAME CONSTANT "&1"
,REQUEST_ID "fnd_global.conc_request_id"
,LAST_UPDATED_BY "FND_GLOBAL.USER_ID"
,LAST_UPDATE_DATE SYSDATE
,CREATED_BY "FND_GLOBAL.USER_ID"
,CREATION_DATE SYSDATE
,INTERFACE_STATUS CONSTANT "N"
,RECORD_STATUS CONSTANT "N"
)

I want to store file name in the column FILE_NAME stated above. I tried with and without constant using "$1", "&1", ":$1", ":&1", &1, $1..... but none of them worked. Please suggest me the solution for this.

Thanks,
Abhay