Oracle-L: RE: External tables

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: Fri, 25 Apr 2003 08:17:00 -0800
Message-ID: <F001.0058962A.20030425081700@fatcity.com>

Jonathan

Where are you getting the idea that the external table definition doesn't allow bad data from? I can only assume you mean the particular table you're thinking of is set up this way, because in general external table definitions DO allow a bad file. Or am I (as usual!) missing something here? Here's an example cut straight from the doc - note the badfile line:

CONNECT / AS SYSDBA;
-- Set up directories and grant access to hr
CREATE OR REPLACE DIRECTORY admin_dat_dir

AS '/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/data'; CREATE OR REPLACE DIRECTORY admin_log_dir

AS '/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/log'; CREATE OR REPLACE DIRECTORY admin_bad_dir

AS '/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/bad'; GRANT READ ON DIRECTORY admin_dat_dir TO hr; GRANT WRITE ON DIRECTORY admin_log_dir TO hr; GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
-- hr connects

CONNECT hr/hr
-- create the external table

CREATE TABLE admin_ext_employees

                   (employee_id       NUMBER(4), 
                    first_name        VARCHAR2(20),
                    last_name         VARCHAR2(25), 
                    job_id            VARCHAR2(10),
                    manager_id        NUMBER(4),
                    hire_date         DATE,
                    salary            NUMBER(8,2),
                    commission_pct    NUMBER(2,2),
                    department_id     NUMBER(4),
                    email             VARCHAR2(25) 
                   ) 
     ORGANIZATION EXTERNAL 
     ( 
       TYPE ORACLE_LOADER 
       DEFAULT DIRECTORY admin_dat_dir 
       ACCESS PARAMETERS 
       ( 
         records delimited by newline 
         badfile admin_bad_dir:'empxt%a_%p.bad' 
         logfile admin_log_dir:'empxt%a_%p.log' 
         fields terminated by ',' 
         missing field values are null 
         ( employee_id, first_name, last_name, job_id, manager_id, 
           hire_date char date_format date mask "dd-mon-yyyy", 
           salary, commission_pct, department_id, email 
         ) 
       ) 
       LOCATION ('empxt1.dat', 'empxt2.dat') 
     ) 
     PARALLEL 
     REJECT LIMIT UNLIMITED;

-- enable parallel for loading (good if lots of data to load)
ALTER SESSION ENABLE PARALLEL DML;
-- load the data in hr employees table

INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,

                       hire_date, salary, commission_pct, department_id,
email) 
            SELECT * FROM admin_ext_employees;

You could change the REJECT LIMIT clause to specify a certain number of rejects before the load fails as well.

Pete
"Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook
"Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA

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

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 -

The query crashes because the external table definition does not allow bad data.
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.

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: 25 April 2003 00:17

>
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: 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: Pete Sharman
INET: peter.sharman_at_oracle.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------