Skip to Main Content

parse CLOBS/BLOBS using "external table" capability

Mike KutzDec 18 2014 — edited Jan 11 2016

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() [] 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"

Post Details
Added on Dec 18 2014