Oracle RDBMS 11gR2 – New PREPROCESSOR Syntax for External Table Use

0

One of the other small but very useful new features in the Oracle 11gR2 database is the addition of a PREPROCESSOR parameter for the create EXTERNAL table statement. This PREPROCESSOR clause gives you the ability to execute an executable or executable (shell) script that handles your data before it gets presented. The PREPOCESSOR clause is part of the ORACLE_LOADER access driver

The Oracle 11gR2 Database Utilities Manual states:

<code>The preprocessor program converts the data to a record format supported by the access driver and then
writes the converted record data to standard output (stdout), which the access driver reads as input.

SQL&gt; CREATE TABLE xtab (recno varchar2(2000))
     2    ORGANIZATION EXTERNAL (
     3    TYPE ORACLE_LOADER
     4    DEFAULT DIRECTORY data_dir
     5    ACCESS PARAMETERS (
     6    RECORDS DELIMITED BY NEWLINE
     7    PREPROCESSOR execdir:'zcat'
     8    FIELDS (recno char(2000)))
     9    LOCATION ('foo.dat.gz'))
    10    REJECT LIMIT UNLIMITED;</code>
<code>
SQL&gt; CREATE TABLE xtab (recno varchar2(2000))
     2    ORGANIZATION EXTERNAL (
     3    TYPE ORACLE_LOADER
     4    DEFAULT DIRECTORY data_dir
     5    ACCESS PARAMETERS (
     6    RECORDS DELIMITED BY NEWLINE
     7    PREPROCESSOR execdir:'uncompress.sh'
     8    FIELDS (recno char(2000)))
     9    LOCATION ('foo.dat.gz'))
    10    REJECT LIMIT UNLIMITED;</code>

So as the example demonstrates, you can uncompress a zipped file to your "liking", manipulating the format before use.

Especially the scripting possibilities makes it a very interesting option. There are, of course, some security limitations, for example the referenced script must be executable and the executable script or referenced binary executable must "exist" in the directory defined by your directory alias.

See for more information the Oracle 11gR2 Database Utilities Manual

A small addition, but a very powerfull one…!

;-)

Share.

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

Comments are closed.