Link to home
Start Free TrialLog in
Avatar of Brent_Bullock
Brent_Bullock

asked on

Can SQL*Loader pass 'infile' file names into a table column?

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 -

LOAD DATA
INFILE 'MEDCO_1.dat'
INFILE 'MEDCO_2.dat'
 
INTO TABLE "IP334"."EOB_LOAD"
Append
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(FILE_NAME CHAR(100),
 ROW_NBR  SEQUENCE (MAX,1),
 EOB_DATA CHAR(4000))

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 -

LOAD DATA
INFILE *
INTO TABLE EOB_FILES
Append
FIELDS TERMINATED BY '%'
(EOB_NAME  CHAR(100),
 EOB_DATA  LOBFILE (EOB_NAME) TERMINATED BY EOF)
BEGINDATA
MEDCO_1.DAT
MEDCO_2.DAT

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"?
ASKER CERTIFIED SOLUTION
Avatar of sathyagiri
sathyagiri
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial