The scheduler has a file watcher object which can monitor a location and start a job when a new file arrives. So that handles the job kick-off:
https://oracle-base.com/articles/11g/scheduler-enhancements-11gr2#file_watcher * Find the delimiter in the fileThis is a HARD problem!
What if a file contains two or more possible delimiters? How do you choose the correct one? How will you resolve issues if the automated process chooses the wrong delimiter? How do you even know it was wrong?
Building a generic "get any file and deal with it" loader is a lot of work.
So the question is do you really receive files which can be in
any format? Or do you have to deal with several different (known) formats?
If you're working with files that have (many) different formats, but you know what they all are, I prefer to create a table and write a package to handle each type. This makes it easy to do file validation (fields have the correct data type, it's not corrupted, etc.) and report corrupt files.
If you really must write a "get any file and deal with it" I recommend upgrading to Oracle Database 18c. This has a couple of features which make this a lot easier:
* Polymorphic table functions (PTFs)
* Inline external tables
With PTFs you can write a delimiter-to-columns converter. For example, this CSV to columns converter I wrote:
https://livesql.oracle.com/apex/livesql/file/content_F99JG73Z169WENDTTQFDQ0J09.html Inline external are "tables" that only exist within your query. These have a "table" name of EXTERNAL, with the columns and external table pasted within it.
For example, this reads the first two columns from test.txt in the directory TMP:
select * from external ( (
c1 varchar2(10), c2 varchar2(10)
)
default directory tmp
location ('test.txt')
);
These save you having to create and drop a table/file.
* Create a log of all the processed filesWhat exactly should go in this log? External tables can create log files with details about the file read. You can create a table storing file names, rows processed, etc. It really depends on what you want to capture.