Here is an example
[oracle@db192 tmp]$ echo "/usr/bin/ls -1 /u01/app" > /tmp/list_files.sh
[oracle@db192 tmp]$ chmod 700 /tmp/list_files.sh
So I have an executable shell file which will list out files in /u01/app
Now I need to reference that in my external table, which also needs a valid location file (even if I do not use it),
[oracle@db192 tmp]$ touch dummy.txt
Now a couple of directories (customise as you see fit)
create or replace directory tmp as '/tmp';
create or replace directory bin as '/tmp';
SQL> select *
2 from external(
3 (filename varchar2(1000 char))
4 type oracle_loader
5 default directory tmp
6 access parameters (
7 records delimited by newline
8 nobadfile nologfile nodiscardfile
9 preprocessor bin:'list_files.sh'
10 fields (filename char(1000))
11 )
12 location ('dummy.txt')
13 reject limit unlimited
14 );
FILENAME
-------------------------------------------
oracle
oraInventory
Once you can list a directory, you can easily get the name of the files you want to load.