2

I am working on a project where (small) XML files need to lead to inserts in an Oracle 11g database.

I could write a tool with a file monitor on the directory the XML files are ftp'd to, translate the XML into insert statements, then connect to the database to run the inserts, but I wonder whether Oracle already has facilities available to do the same.

So I am looking for an Oracle 11g (or separate) tool that can automatically detect arrival of new files in some directory, and process these as an XML upload into the Oracle database (of course, the XML format has to conform to Oracle XML upload requirements).

Improve this question

2 Answers 2

Reset to default
1

If you are using Oracle Database 11gR2 the Oracle Scheduler has been enhanced to include a file watcher.

About File Watchers

A file watcher is a Scheduler object that defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file. The message contains the information required to find the file, open it, and process it.

A file watcher can watch for a file on the local system (the same host computer running Oracle Database) or a remote system. Remote systems must be running the Scheduler agent, and the agent must be registered with the database.

You can use this functionality to "kick off" the routine you need to insert the XML into database tables.

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

2 Comments

Definitely worth a try! I will check this out but I am confident it will work.
I have been looking for the step that has to be executed after the file watcher has triggered a job, namely running SQL*LOADER on the XML file - but I could not find this. Am I mistaken that Oracle can handle XML uploads in canonical ROWSET/ROW/COLUMNname form? So what's the syntax of the sqlldr control file? Note that I might have to put this up as a separate question :-)
0

I would not recommend start using it just for this, but one possibility: Oracle Fusion Middleware (SOA and OSB)

http://niallcblogs.blogspot.nl/2010/06/osb-11g-leveraging-oracle-file-adapter.html

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.