Oracle External Tables or What I did on my Holidays

This week’s missive is coming to you from the netbook. Deb and I have pushed the boat out this year and we’re currently in Canada for our holiday.
This has nothing at all to do with Oracle External Tables, but does explain the flavour of the examples that follow.

The Anti-Kyte on holiday...the costume didn't catch on

What are they?

External tables are a mechanism by which you can load flat file data into your database, or by which you can export data from your database.

Hold on, what about SQLLoader for data uploads ? And what exactly is wrong with Data Pump for exports ? Come to that, why not just use UTL_FILE for reading/writing files ?
Well, these are all pertinent questions.
In fact External tables make use of the SQL*Loader API for loading data and the Datapump API for exporting data.
They are essentially another means of achieving what you can do with all of these tools. Which one you choose will no doubt depend on what you want to accomplish.
The big difference is that you can use External Tables to query data not stored in the database wheras the other tools listed here are more concerned with getting data into the database first.
As I want to knock out a blog post about External Tables, well, you can guess which one I’ll be using here.
Our starting point is a table that contains all of the places I’m going to visit, together with the dates I’ll be there :

CREATE TABLE holiday_destinations(
    city_name VARCHAR2(30),
    flight_time DATE,
    arrival_time DATE)

INSERT INTO holiday_destinations(
    city_name, flight_time, arrival_time)
VALUES('HALIFAX', TO_DATE('20-08-11 12:35', 'DD-MM-RR HH24:MI'),
    TO_DATE('20-08-11 15:15', 'DD-MM-RR HH24:MI'))

INSERT INTO holiday_destinations(
    city_name, flight_time, arrival_time)
VALUES('MONTREAL', TO_DATE('22-08-11 12:25', 'DD-MM-RR HH24:MI'),
    TO_DATE('22-08-11 12:58', 'DD-MM-RR HH24:MI'))

INSERT INTO holiday_destinations(
    city_name, flight_time, arrival_time)
VALUES('TORONTO', TO_DATE('24-08-11 11:00', 'DD-MM-RR HH24:MI'),
    TO_DATE('24-08-11 12:15', 'DD-MM-RR HH24:MI'))

INSERT INTO holiday_destinations(
    city_name, flight_time, arrival_time)
VALUES('OTTOWA', TO_DATE('27-08-11 11:10', 'DD-MM-RR HH24:MI'),
    TO_DATE('27-08-11 12:10', 'DD-MM-RR HH24:MI'))

The data we want to upload relates to the flights between these cities. We’re going to put these into a csv file :

HALIFAX, 20-AUG-2011, 22-AUG-2011
MONTREAL, 22-AUG-2011, 24-AUG-2011
TORONTO, 24-AUG-2011, 27-AUG-2011
NIAGARA FALLS, 25-AUG-2011, 25-AUG-2011
OTTOWA, 27-AUG-2011, 30-AUG-2011

Setting up a directory

The first step is to tell the database where to look for the data we’re going to load into our external table. This involves defining a directory object to point to the os directory where the file is going to reside.
This is exactly the same step that you need to take when reading/writing files using UTL_FILE so, in best Blue Peter tradition, here’s one I made earlier :

cd /usr/lib/oracle/xe/app/oracle/admin/XE
sudo su oracle
mkdir ext_tabs

You should now have a directory owned by oracle to which oracle has read and write privileges.

Next, copy the .dat file to this directory

sudo cp $HOME/itinerary.dat /usr/lib/oracle/xe/app/oracle/admin/XE/ext_tabs/.

Now, in the database, set up a directory


The owner of the directory object should already have READ and WRITE privileges.

The External Table (Part 1) – load

We need to tell the database how to hold the data in the csv file. This is where we create the external table

CREATE TABLE itinerary_ext(
    location VARCHAR2(30),
    from_date DATE,
    leave_date DATE)
        type oracle_loader
        default directory myfiles
        access parameters ( 
            records delimited by newline
            fields terminated by ','
            missing field values are NULL
            reject rows with all null fields( location, from_date, leave_date)
        location( 'itinerary.dat')

The table type can be either oracle_loader or oracle_datapump. We’re using the first option here as we want to read data in.

The External Table Upload

If we just want to query the data, then we now simply have to issue a SELECT statement

FROM itinerary_ext

If you get an error like this :

ERROR at line 1: 
ORA-29913: error in executing ODCIEXTTABLEOPEN callout 
ORA-29400: data cartridge error 
KUP-04063: unable to open log file ITINERARY_EXT_1718.log 
OS error Permission denied 
ORA-06512: at "SYS.ORACLE_LOADER", line 19 

then it’s more than likely a permissions issue on the os. Check that the oracle owner (usually oracle) has write permissions on the directory (at the os level rather than in the database) where the csv file is located.

Assuming all is working as expected, we can use this table in any valid query :

SELECT hd.city_name, ie.from_date, ie.leave_date 
FROM holiday_destinations hd, itinerary_ext ie 
WHERE ie.location = hd.city_name 

OK, so there are some limitations, you can’t specify indexes on the external table for example, and direct DML is also not going to work.
If we want to play with the data inside the database, we’re going to have to load it into a permanent segment…or are we ?
Say you had some external data that you wanted to manipulate in some way and dump the output back onto the OS without the overhead of a permanent segment such as a holding table ? Well, this is an option using only external tables…provided you want to dump the results into another Oracle Database. This is where the datapump type comes in.

External Table (Part 2) – Dump

Instead of creating a permanent table, where going to create a Global Temporary Table :

CREATE GLOBAL TEMPORARY TABLE holiday_itinerary_gtt(
    city_name VARCHAR2(30),
    from_date DATE,
    leave_date DATE,
    flight_time DATE,
    arrival_time DATE)

OK, so the structure IS permanently held in the Data Dictionary, but the data itself is transient and lives exclusively in the PGA.

Next, we’re going to need a procedure to do the transformation and dump the results out onto the os. This will require us to create an external table on the fly…

    l_dummy PLS_INTEGER;
    l_exists BOOLEAN;
    CURSOR c_tab_exists IS
        SELECT 1
        FROM user_tables
        WHERE table_name = 'EXP_ITINERARY';
    l_out_tab VARCHAR2(4000) :=
        ||'( TYPE oracle_datapump '
        ||'  DEFAULT DIRECTORY myfiles '
        ||q'[  LOCATION( 'flights.dmp')]'
        ||') AS SELECT * FROM holiday_itinerary_gtt';  
    -- Check to see if our external table currently exists.
    -- If so, we need to drop it
    OPEN c_tab_exists;
    FETCH c_tab_exists INTO l_dummy;
    l_exists := c_tab_exists%FOUND;
    CLOSE c_tab_exists;
    IF l_exists THEN
        EXECUTE IMMEDIATE 'DROP TABLE exp_itinerary';
    END IF;
    -- Now populate the GTT
    INSERT INTO holiday_itinerary_gtt( 
        city_name, from_date, leave_date,
        flight_time, arrival_time)
    SELECT ie.location, ie.from_date, ie.leave_date,
        hd.flight_time, hd.arrival_time
    FROM itinerary_ext ie, holiday_destinations hd
    WHERE ie.location = hd.city_name;
    -- Now create an external table on the fly to dump out the results
    EXECUTE IMMEDIATE l_out_tab;

For the sake of clarity, the code for creating the external table is reproduced here :

    TYPE oracle_datapump 
    LOCATION( 'flights.dmp') 
    AS SELECT * FROM holiday_itinerary_gtt 

Just run the procedure…

exec export_flights_pr

You should now have a file called flights.dmp in your myfiles directory.

 ls -l flights.dmp 
-rw-r----- 1 oracle dba 12288 2011-08-25 02:56 flights.dmp 

So now we have a dump file, should be simple enough to import into the target database, shouldn’t it ? Not using datapump import, apparently, which gives you an ORA-31619 error complaining that the dump file wasn’t created by a datapump export. Honestly, there’s no pleasing some database utilities !

Sigh, I suppose we’ll just have to create another external table on the target database. This time, however, we specify the type as datapump…

CREATE table imp_flights (
    city_name VARCHAR2(30), 
    from_date DATE, 
    leave_date DATE,
    flight_time DATE, 
    arrival_time DATE)
        TYPE oracle_datapump
        DEFAULT DIRECTORY myfiles

Now it should all work as expected…

SELECT * FROM imp_flights

Right, sun cream, bear repellent, directions to the Moulson Brewery. I’m all set.

About these ads

2 thoughts on “Oracle External Tables or What I did on my Holidays

  1. Thanks for that, I hadn’t considered datapump-based external tables before.

    When you do the transform and output the results via datapump, why do you use a GTT, instead of basing the table directly on the query? If you skipped the GTT the data would no longer need to be duplicated in the PGA, the query results could go straight out to disk.

    • Jeffrey,

      You’re absolutely right, the GTT is in fact completely unnecessary and you could reference the itinerary_ext table directly in the creation statement for exp_itinerary. Looking back, the table names I used could’ve been less confusing as well.
      Of course, the GTT does make the code a little more readable ( well, I think so, anyway). There’s also my own personal dislike of using too much dynamic SQL, especially DDL statements which are always so much fun to debug.
      When all’s said and done however, I just have to hold my hands up and say, erm, I missed that one. Well spotted and thanks for pointing it out.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s