0

Situation

I have a CSV file called inventory.csv located on an Oracle database server (2008 R2 Enterprise Edition Windows Server). This CSV file is used as an Oracle external table.

Every hour, a scheduled task (Windows Task Scheduler) executes a .bat file that copies over an updated version inventory.csv, overwriting the original.

The data is then used by a reporting application.

Problem

The application that uses the data in inventory.csv has no way of knowing when the data was last updated.

Ideally, I'd like the "last updated date" to be accessible as a column in the table.

One possible solution is to trigger a logging of the current date/time in a separate file, an then referencing that as an external table as well. However, this solution has too many moving parts, and I'd prefer something simpler, if possible.

I know that the CSV file itself knows when it was created...I'm wondering if there is any way for the Oracle external table to read the "Created" date from the CSV file properties?

Or any other ideas?

chester
  • 297
  • 2
  • 13
  • [This scenario is similar](http://stackoverflow.com/a/29106343/266304), but appends the file name as a column; using the file's created date instead wouldn't be difficult. But it's using bash, and I'm not sure the best way to get the information you need through a Windows batch (or powershell, or whatever) script. The `preprocessor` part will be very similar though, as Justin already mentioned. – Alex Poole Sep 06 '16 at 17:38

1 Answers1

2

What version of Oracle?

If you are using 11.2 or later, you can use the preprocessor feature of external tables to run a shell script/ batch file on the file before it is loaded. My bias would be to go for simplicity-- have the preprocessing script grab the date, store it to a separate file, and have a separate external table that loads and exposes that data. That's likely easier than adding the date to every row.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384