Oracle File system Extension
OFsX@sourceforge.org
Intro
- Problem: How can Oracle PL/SQL applications read, write, move or copy arbitrarily large files to or from arbitrary file system locations?
- Solution: This application (OFsX) uses PL/SQL, Oracle AQ & Spring Integration
to handle reading, writing, copying, moving & deleting of files from Oracle PL/SQL in a general way.
Overview
- OFsX utilizes:
- OFsX will run in any Java application container that supports SI. This version uses EclipseRT Virgo (Virgo).
- In order to write, copy, move or delete files from PL/SQL, two steps are required:
- A PL/SQL package,
OFSX_UTL, is used to enqueue a message containing fully qualified file path(s). If the file is to be created, the contents of the file are in the Message<String> payload.
- The OFsX SI component dequeues the message & dispatches it per an
operation header: delete, write, etc.
- File reading involves three steps:
- A PL/SQL package,
OFSX_UTL, is used to enqueue a message with operation header read telling the OFsX SI app to read data from an arbitrary file system location.
- The OFsX SI component then loads the data into
T$OFSX_DATA.
- Thereafter the calling app can process its data in
T$OFSX_DATA.
USE
- WRITE | CREATE: Any PL/SQL application that needs to write data to a file on the file system must invoke
ofsx_utl.write, which enqueues a Message<String> with destination file system location header & file contents payload. The message is dequeued by OFsX SI app & payload written to location indicated in header. TODO: FLAG for existing files to be overwritten?
- MOVE | RENAME: Any PL/SQL application that needs to move a file on the file system must invoke
ofsx_utl.move, which enqueues a Message<String> with source & destination file system location headers. The message is dequeued by OFsX SI app & source file is moved to destination location.
- COPY: Any PL/SQL application that needs to copy a file to a new file system location must invoke
ofsx_utl.copy, which enqueues a Message<String> with source & destination file system location headers. The message is dequeued by OFsX SI app & source file is copied to destination location.
- DELETE: Any PL/SQL application that needs to delete a file from the file system must invoke
ofsx_utl.remove, which enqueues a Message<String> with source & destination file system location headers. The message is dequeued by OFsX SI app & source file is copied to destination location.
- READ | GET | IMPORT:
- Any PL/SQL application that needs to read a file from the file system must invoke
ofsx_utl.import.A, which enqueues a Message<String> with source file system location header.
- The message is dequeued by OFsX SI app, which calls
oft_utl.import.B to load the specified source file into OFsX.T$OFSX_DATA.
- Later, the calling app can process the file data in
OFsX.T$OFSX_DATA.
TODO
- Use Maven to deploy builds to SF.net
- Similarly, get ofsx-[version].jar into a Maven repository. Some links:
- http://docs.codehaus.org/display/MAVENUSER/MavenAndSourceforge
- http://stackoverflow.com/questions/16487/how-can-i-deploy-artifacts-from-a-maven-build-to-the-sourceforge-file-release-sy
- Deploying site to SF.net: http://maven.apache.org/plugins/maven-site-plugin/examples/site-deploy-to-sourceforge.net.html
- In alternative, write instructions for local Maven install
- Update all Maven plug-ins, etc
- Issue: XE can't use JMS types. See PEbell. So,
- Don't develop on XE?
- Switch to ADT?
- Write end users' step-by-step HOWTO for each operation: DELETE, MOVE, etc.
- Create Dia SVGs showing interaction of all components for each step
- Write example PL/SQL app & JUnit tests to execute
- Replace Log4J with more standard Java logging?
- Runtime logging changes? Write JMX for controlling log levels? Or some other means?
- Test deployments in other containers
- Replace T$OFSX_DATA table with a blocking deq that waits for response, where response carries requested file data?
Links