SQLLDR control file: Loading multiple files

Question

Iam trying to load several data files into a single table. Now the files themselves have the following format:

                          file_uniqueidentifier.dat_date

My control file looks like this

    LOAD DATA
     INFILE '/home/user/file*.dat_*'
       into TABLE NEWFILES
         FIELDS TERMINATED BY ','
          TRAILING NULLCOLS
                (
                     FIRSTNAME  CHAR NULLIF (FIRSTNAME=BLANKS)
                    ,LASTNAME   CHAR NULLIF (LASTNAME=BLANKS)
                             )

My SQLLDR on the other hand looks like this

                sqlldr control=loader.ctl, userid=user/pass@oracle, errors=99999,direct=true

The error produced is SQL*Loader-500 unable to open file (/home/user/file*.dat_*) SQL*Loader-553 file not found

Does anyone have an idea as to how I can deal with this issue?

Solution 1

SQLLDR does not recognize the wildcard. The only way to have it use multiple files to to list them explicitly. You could probably do this using a shell script.

Solution 2

Your file naming convention seem like you can combine those files in to one making that one being used by the sqlldr control file. I don't know how you can combine those files into one file in Unix, but in Windows I can issue this command

copy file*.dat* file.dat

This command will read all the contents of the files that have the names that start with file and extension of dat and put in the file.dat file.