SQL Loader need to insert the input filename into output table
476743Jun 30 2009 — edited Jun 30 2009Hi All,
I've a small problem with my SQL Loader query. My sql loader should read a file and write the data into a file. Also this sql loader should read the filename and write it into the same table. Here's my control file code:
LOAD DATA
APPEND
INTO TABLE XXMW_STG_SOH_HEADER_UK
WHEN (1:4) = '7010'
TRAILING NULLCOLS
(WH POSITION(5:6)
,ITEM POSITION(9:26)
,PRODUCT_STATUS POSITION(33:34)
,BALANCE_ON_HAND POSITION(35:43)
,TO_SHIP_QTY POSITION(71:79)
,RUSH_TO_SHIP_QTY POSITION(80:88)
,RESERVED_QTY POSITION(175:183)
,SNAPSHOT_DATE POSITION(134:143) CHAR "TO_DATE(:SNAPSHOT_DATE,'YYYY-MM-DD')"
,SNAPSHOT_TIME POSITION(144:151) CHAR "TO_DATE(:SNAPSHOT_TIME,'HH24:MI:SS')"
,PROCESSED_IND CONSTANT "N"
,PROCESSED_DATETIME SYSDATE
,_FILENAME POSITION(184) CHAR TERMINATED BY WHITESPACE_
)
My program should read the filename dynamically (means a shell script is calling this .ctl file which is reading multiple input files) and insert into the filename field in xxmw_stg_soh_header_uk table.
Please let me know for any questions/clarifications.
Regards,
Debabrata