Quality Control: Check file names in a folder against filenames in a table

I have a records system in an Oracle database. The records system is used to retrieve pdf, jpeg, video documents, etc.

The design is quite simple: there is a table called infrastructure_records that has a column with hyperlinks to files:

+------------+------------------------------------------+
| project_id |                file_path                 |
+------------+------------------------------------------+
| Project 1  | X:\Records_System\Folder A\Project 1.pdf |
| Project 2  | X:\Records_System\Folder B\Project 2.jpg |
| Project 3  | X:\Records_System\Folder C\Project 3.mpg |
+------------+------------------------------------------+

We use a front-end that creates a clickable hyperlink out of the file paths. Using the hyperlink, users can navigate to the files.

Question:

I want to check the files on the network drive against the list of files in the database table.

If I could get a list of files from the network into a database table, then I could easily query for errors.

However, I'm not sure what the best way would be to create the list of files in an Oracle table.

• There are approximately 60,000 files on the network drive, nestled in a variety of subfolders
• The files don't exist on the same server as the Oracle database (I'm not an I.T. guy, so I'm pretty clueless about this sort of thing). However, I do have the drive mapped in Windows Explorer on the computer that I would be running the query from.
• This QC operation would be run about once per month.
• Performance isn't a huge concern. The list of files doesn't need to be "live", although that would certainly be a plus.
• I can think of quick-and-dirty ways to do this using VBA scripting in MS Office or the like. But, it has occurred to me that there might be a more professional or out-of-box way to do this (perhaps using built-in Oracle functionality).

How can a I elegantly insert a list of files on a network drive into an Oracle table?

1) Create a batch file in (say) C:\TEMP called dirlist.bat

@echo off
dir /s /b X:\Records_System

2) Create an external table that will pre-processing using that file

CREATE TABLE dir_listing
( file_name VARCHAR2(255)  )
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY temp
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    PREPROCESSOR temp: 'dirlist.bat'
    FIELDS TERMINATED BY WHITESPACE
   )
  LOCATION ('dummy_file.txt')
  )
REJECT LIMIT UNLIMITED;

where 'temp' is an oracle directory

create directory temp as 'c:\temp';
grant read, write, execute on directory temp to ...

and 'dummy_file.txt' is any existing file

Now you just query table dir_listing like any other table to get a list of the files