News, views, tips and tricks on Oracle and other fun stuff

Neat Oracle Database 11g Release 2 Feature: Preprocessing External Tables

Arup Nanda:

External tables enable users to access data in text files, immediately eliminating the need to load input text files to intermediate tables for processing—saving both time and storage space. Now, with Oracle Database 11g Release 2, intermediate processing of any kind—such as decompression of compressed input files—is eliminated, further saving time and storage, not to mention a change to the existing code.

But the power of inline preprocessing is not limited to decompression alone. It can be applied to any type of prior processing required, as long as it produces an output that can be parsed by the external table. The directory listing is just one small demonstration of this rich capability of preprocessing in external tables. You can also use it to massage datafiles to fit a specific format, append or augment data from multiple sources dynamically—without creating any intermediate storage—or even send an e-mail when a specific text file is accessed by an external table. What you can do with inline preprocessing is limited only by your imagination.

Greg Rahn:

Before External Tables existed in the Oracle database, loading from flat files was done via SQL*Loader. One option that some used was to have a compressed text file and load it with SQL*Loader via a named pipe. This allowed one not to have to extract the file, which could be several times the size of the compressed file. As of, a similar feature is now available for External Tables (and will be in This enhancement is a result of Bug 6522622 which is mentioned in the Bugs fixed in the Patch Set note.

Filed in Links, Oracle on 24 Mar 11 | Tags: ,

Reader's Comments

  1. |

    Interesting feature. It is a pity though that the OPTIONS keyword that was hiowincluded in somehow is not in or even in

    This makes the feature suddenly not so interesting anymore.