Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-07-25
1
Medium Priority
?
1,764 Views
Last Modified: 2013-12-19
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"?
0
Comment
Question by:Brent_Bullock
1 Comment
 
LVL 14

Accepted Solution

by:
sathyagiri earned 750 total points
ID: 19568900
You could create a function that will return the file name from the EOB_FILES table and call that function in your 1st control file

create or replace function get_file_name
return varchar2
is
begin
// Logic here
end;
/

In the control file

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


0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month10 days, 7 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question