I feel that, as time goes on, more and more applications will be required to process larger and larger data files.
For a web applications (eg APEX-based), the server gets the file, processes it, and then moves on but never needs that file (clob/blob) again.
External Tables (especially with the pre-processor) can be used to efficiently process the data files; however, the External Table needs to be used in a sequential fasion.
This limits the usefulness of External Tables when used as the processor component in a multi-user environment (eg web based applications).
I see that Oracle already has:
- table() for processing data returned by [pipelined] functions into a table-like stucture
- xmtable() for processing xml data into a table-like structure
- json_table() [12.1.0.2] for processing JSON data into a table-like stucture
My idea is to have some sort of SQL clause (eg blob_table())such that:
- it has a similar syntax to what has already been done (table(),xmltable(),json_table())
- it can be used in a FROM clause
- it uses CLOBs/BLOBs for LOCATION instead of file names on the file system ( main request )
- still allows usage of the pre-processor ( <-- big wish )
For first iteration, using LOBs through a DB-link could be "out-of-scope"