Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: External tables

Re: External tables

From: Jonathan Lewis <>
Date: Fri, 25 Apr 2003 00:26:47 -0800
Message-ID: <>

So if a couple of lines in the file acquired some dirty data (e.g. a tab character pushes a line out of position) there are two possible options -

  1. The query crashes because the external table definition does not allow bad data.
  2. The query returns the wrong answer because the external table definition allows for an unlimited number of errors, so the line is ignored.

Historically, your client may have had code to alert an administrator at load time, so that the data could be checked and cleaned before the user accessed it.

I've found a good use for external tables, so I have nothing against the concept per se - however, I do think it is important to throw a little cold water around when people suggest that an external table definition is a substitute for a sql load. In many cases the effect of the external table is to move the trivial part of the load into database.


Jonathan Lewis

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:

____UK_______April 22nd
____USA_(FL)_May 2nd
____Denmark__May 21-23rd

Three-day seminar:
____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ

The client
> I'm referring to simply kept the same file name each time, and the overhead
> of loading the material went down from occurring every hour to occurring
> only when the table was queried. Of course, there's the issue that the
> overhead of the load now occurred at the time of the query, but for this
> particular customer that wasn't so much of a concern. >

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Apr 25 2003 - 03:26:47 CDT

Original text of this message