We have many files available in our Linux mount point and wanted to load them into the table. The solution I'm seeking is to load all the files(with no-header, a different set of columns in each file, comma separated and new line char is available).

Sample file data:

files1.csv

1,"Test1"
2,"Test2"

-----
files2.csv

1,123,"Case0"
2,456,"MyName"

-----
files3.csv

1234234,"2024-01-01","foo"
5894234,"2024-02-01","foo3"


I'm looking for a way to load these files in a single table as given below. Is there a way we can achieve this using SQL Loader?


Oracle Table:

Create table generic_files(COLUMN_1 VARCHAR2(4000), COLUMN_2 VARCHAR2(4000), COLUMN_3 VARCHAR2(4000), FILE_NAME VARCHAR2(4000), INSERT_DT DATE default SYSDATE)

COLUMN_1. | COLUMN_2. | COLUMN_3 | FILE_NAME. | INSERT_DT (will have sysdate)
1 Test1 null files1.csv
2 Test2 null files1.csv
1 123 Case0 files2.csv
2 456 MyName files2.csv
1234234 2024-01-01 foo files3.csv
5894234 2024-02-01 foo3 files3.csv

SQL> select *
  2  from   external (
  3        ( c1 varchar2(20),
  4          c2 varchar2(20),
  5          c3 varchar2(20),
  6          c4 varchar2(20),
  7          c5 varchar2(20)
  8        )
  9        type oracle_loader
 10        default directory ctmp
 11        access parameters
 12        ( records delimited by newline
 13          nobadfile
 14          nologfile
 15          nodiscardfile
 16          fields terminated by ',' MISSING FIELD VALUES ARE NULL (c1,c2,c3,c4,c5)
 17         )
 18         location ( 'file1.dat', 'file2.dat' )
 19  reject limit unlimited ) ext;

C1                   C2                   C3                   C4                   C5
-------------------- -------------------- -------------------- -------------------- --------------------
1                    "Test1"
2                    "Test2"
1                    123                  "Case0"
2                    456                  "MyName"