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.
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
CREATE DIRECTORY myfiles AS '/usr/lib/oracle/xe/app/oracle/admin/XE/ext_tabs' /
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) ORGANIZATION EXTERNAL ( 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
SELECT * 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) ON COMMIT PRESERVE ROWS /
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…
CREATE OR REPLACE PROCEDURE export_flights_pr AS 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) := 'CREATE TABLE exp_itinerary ORGANIZATION EXTERNAL ' ||'( TYPE oracle_datapump ' ||' DEFAULT DIRECTORY myfiles ' ||q'[ LOCATION( 'flights.dmp')]' ||') AS SELECT * FROM holiday_itinerary_gtt'; BEGIN -- -- 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; END; /
For the sake of clarity, the code for creating the external table is reproduced here :
CREATE TABLE exp_itinerary ORGANIZATION EXTERNAL( TYPE oracle_datapump DEFAULT DIRECTORY myfiles LOCATION( 'flights.dmp') ) AS SELECT * FROM holiday_itinerary_gtt /
Just run the procedure…
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) ORGANIZATION EXTERNAL ( TYPE oracle_datapump DEFAULT DIRECTORY myfiles LOCATION('flights.dmp') ) /
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.