RE: External tables

External tables are generally much quicker than UTL_FILE for reading data. However, they are currently read-only

Kevin

-----Original Message-----
Sent: Friday, April 25, 2003 4:37 AM
To: Multiple recipients of list ORACLE-L

Can external tables be used instead of UTL_FILE to read and write external files?
If so, is it quicker than UTL_FILE?

John

-----Original Message-----
Sent: 25 April 2003 00:57
To: Multiple recipients of list ORACLE-L

Re dirty data: Isn't it just going to go into the bad/discard file? Not sure at the moment, and no time to play with it.

As for handling the bad file, it too can be an external table.

How you handle the 'bad' data is highly site dependent, but at least you can easily access it from Oracle.

Jared

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> Sent by: root_at_fatcity.com
 04/24/2003 03:01 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: External tables

Another point missed so far is that
if your SQL to the external table hits
some dirty data, then it bombs out
with an Oracle error, and if you've been busy running an insert from it the next
thing that happens is a likely to be a
massive rollback.

You also have to deal with the discard/bad file (in the event that your external table definition includes a 'bad limit' and doesn't crash your process) so you haven't really got rid of much of the infrastructure that you would need if you were to use sqlldr - only the SQLLOAD command and a
couple of lines of shell to check the exit status from SQLLOAD and grep/awk the
rows-loaded count from the log.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  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:
http://www.jlcomp.demon.co.uk/tutorial.html

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

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 24 April 2003 19:16

> The replies so far have failed to mention that parallel loading with
> external tables is easy. With sqlloader, it ain't so easy.
>
> With external tables, it is also easy to do additional processing
via
> SQL when you load. In many cases you could avoid extra steps
required
> in sqlloader, such as loading to a temp table, then massaging your
data
> and loading to a perm table.
>
> You can skip the temp table with external tables.
>
> Jared
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Dunn
  INET: john.dunn_at_sefas.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kevin Toepke
  INET: ktoepke_at_rlcarriers.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).