Accessing Created date of a CSV file using an Oracle External table

Question

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?

This scenario is similar, 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
– Alex Poole, Commented Sep 6, 2016 at 17:38

Justin Cave · Accepted Answer · 2016-09-06 17:12:04Z

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.

answered Sep 6, 2016 at 17:12

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Accessing Created date of a CSV file using an Oracle External table

Situation

Problem

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Situation

Problem

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related