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"?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.