Can SQL*Loader pass 'infile' file names into a table column?
Posted on 2007-07-25
Oracle 10g / SQL*Loader
I'm trying to create a SQL*Loader control card that will load a file into a two column table, where the first column contains the file name (repeating for each row of data) and the second column contains the unparsed data rows. Is there any way to make a control card that will pass the 'infile' file name into a table column? I imagine a control file like this -
INTO TABLE "IP334"."EOB_LOAD"
FIELDS TERMINATED BY '|'
ROW_NBR SEQUENCE (MAX,1),
which would somehow know to put the 'infile' file names into the FILE_NAME column, without my having to hardcode the values as constants.
As an alternative, since I have succeeded in loading the file name into a differnt table, with the file name column associated with a CLOB column containing the full file using the "begindata" option in this control card -
INTO TABLE EOB_FILES
FIELDS TERMINATED BY '%'
EOB_DATA LOBFILE (EOB_NAME) TERMINATED BY EOF)
is there a way to pass the EOB_NAME column populated in the second control card, into the first control card, that would function along the lines of "select 'infile '||eob_name from eob_files"?