Sample Header Ad - 728x90

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

0 votes
1 answer
273 views
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?**
Asked by User1974 (1527 rep)
Jan 9, 2018, 08:58 PM
Last activity: Jan 13, 2018, 04:12 PM