8/22/2001 -- Last month I introduced you to the new multi-table INSERT capabilities in Oracle 9i. They provide you with the ability to add data to more than one table from a single source or to add data to certain tables based upon the values found in the source. The flexibility this provides can reduce or eliminate the need to create anonymous PL/SQL blocks or procedures for moving data from an OLTP system to a data warehouse or data mart, or for massaging data during a transformation. Two other features provided in 9i also work along these lines: pivoting inserts and the ability to create external tables.

Pivoting INSERTs
The concept behind a pivoting insert is quite simple: You have data that exists in one row of a table that needs to be split into multiple rows when inserted into a second table. This is something that is quite common in data transformations from OLTP systems to data warehouses. In my case, I ran across the need for a pivoting insert due to a badly designed table structure at a client.

The client in question asked one of their junior developers (a first year co-op student) to design a simple database to track expenses for their employees. They needed to track separate amounts for hotel, car, taxi, airfare, meals and other incidental expenses (there were about 20 types of expenses) and, for each expense, the amount of GST charged (since they were based in Canada) as well as the currency that the expense is claimed in (because they operate internationally). The table that was designed had a structure similar to the following CREATE TABLE statement:

   CREATE TABLE Expenses (
                ExpenseID       number(8)               NOT NULL        PRIMARY KEY,
                Purpose         varchar2(400)   NOT NULL,
                Currency        char(3)                 NOT NULL,
                Car                     number(8,2)     NULL,
                CarGST          number(8,2)             NULL,
                Airfare         number(8,2)             NULL,
                AirfareGST      number(8,2)             NULL,
                Taxi            number(8,2)             NULL,
                TaxiGST         number(8,2)             NULL
                Hotel           number(8,2)             NULL,
                HotelGST        number(8,2)             NULL,
                … and so on …

Now, if you were someone without any relational background, you have to admit that the idea seems all right as a concept - you have each expense type available and the GST split out. The structure runs into problems if you want to expand the type of expenses to track or a different tax structure need to be put in place and the like. This is exactly what happened to these folks (and the person that did this was no longer around). Logically, to fix this problem you create several tables (ExpenseHeader, ExpenseDetails, ExpenseCategories, and maybe others) then take the data from the original table and break it up into parts of each of the new tables. To do this, Oracle 9i's pivoting insert fits the bill beautifully.

The pivoting insert allows you to issue a statement like the following (combining pivoting and conditional multi-table inserts):

INSERT ALL
     WHEN 1=1 
        INTO ExpenseHeader 
        VALUES (ExpenseID, Purpose, Currency)
        WHEN NVL(Car,0) > 0 
        INTO ExpenseDetails 
        VALUES (ExpenseID, 'CAR', Car, NVL(CarGST,0))
        WHEN NVL(Airfare,0) > 0
        INTO ExpenseDetails 
        VALUES (ExpenseID, 'AIRFARE', Airfare, NVL(AirfareGST,0))
        WHEN NVL(Hotel, 0) > 0
                INTO ExpenseDetails 
                VALUES (ExpenseID, 'HOTEL' Hotel, NVL(HotelGST,0))

                … more conditions and inserts here …

SELECT   ExpenseID, Purpose, Currency, Car, CarGST, Airfare, 
          AirfareGST, Hotel, HotelGST, Taxi, TaxiGST, …
FROM Expenses;

In the above example, each row from the original Expenses table would be read by the INSERT ALL statements and then INSERTs would be performed on the ExpenseHeader table for each row in the expenses table (because the condition is WHEN 1=1), and then, depending on the expense type and whether its dollar value was greater than 0 (NVL function is used because the data allowed NULLs), an INSERT into the ExpenseDetails table is made, with the appropriate category code also properly put in the ExpenseCategory field that exists in the ExpenseDetails table. In this way, each row read from the original Expenses table would add one row to the ExpenseHeader table and one or more rows into the ExpenseDetails table, depending on which expenses were claimed.

But The Data Isn't in Oracle!!!
So the pivoting INSERT, and last month's multi-table inserts work great when the data is in an Oracle database, but what if the data is in a flat file that you may need to load into an Oracle database? How would you like to eliminate SQL*Loader and access the data file directly on disk?? You can now with Oracle 9i's external tables.

An external table in Oracle 9i is a pointer to a definable structure of data in a flat file on the operating system hard disk. In other words, Oracle 9i now allows you to create a table that is a pointer to a flat file, and then tell Oracle 9i what the structure of that flat file should be interpreted as. External tables are read-only and cannot be modified using the INSERT, UPDATE, or DELETE statements because Oracle does not manage but only reads their content. As the following CREATE TABLE syntax for an external table shows, you are essentially combining a CREATE TABLE statement with a SQL*Loader control file to allow Oracle to access data in a flat file directly without you having to load it with SQL*Loader into a pre-defined Oracle table.

   CREATE TABLE Instructors (
                InstructorID    number, 
           LastName              char(20), 
           FirstName             char(20),
           InstructorType        char(5),
           Rate                      number)
        ORGANIZATION EXTERNAL (
                   TYPE ORACLE_LOADER
                   DEFAULT DIRECTORY data_dir
                   ACCESS PARAMETERS (
                            RECORDS DELIMITED BY NEWLINE
                            FIELDS TERMINATED BY ','
                            BADFILE 'BadInstData'
                            LOGFILE 'InstLogfile' (
                                    InstructorID    INTEGER,
                                    LastName                CHAR(),
                                    FirstName               CHAR(),
                                    InstructorType  CHAR(),
                                    Rate                    INTEGER))
                   LOCATION ('InstData.txt'))
        PARALLEL 6
        REJECT LIMIT 50;

There are a number of interesting elements in this syntax. First, the ORGANIZATION EXTERNAL clause of the CREATE TABLE statement tells Oracle 9i that the table is going to be an external file. Second, you need to make use of the Oracle DIRECTORY object to point to the physical path of the directory where the file is stored and then reference the logical directory name in the DEFAULT DIRECTORY clause of the statement (i.e., you must issue a CREATE DIRECTORY command to create the logical name before you can issue the CREATE TABLE statement). Third, the TYPE ORACLE_LOADER tells Oracle 9i that the file is something that SQL*Loader can read and the remaining elements of the CREATE TABLE statement are SQL*Loader-style control file commands to describe the text file structure and name -- a second type of ORACLE_INTERNAL is also supported for Import/Export files.

The Only Limits Are in Your Mind (or Something Like That…)
Once you have created an external table, you can use SELECT statements to access the data, apply WHERE conditions if needed and so on. In other words, you now have a direct link to a disk file that can be treated as a table in Oracle and used in joins. Think of the possibilities for interfacing with third-party application, web apps, and many others. The possibilities are endless -- not to mention that since you can use parallel query processes to access the external table, this can also be a way to perform bulk loads using pivoting and conditional multi-table inserts into a data warehouse without ever creating temporary tables in Oracle or having to use SQL*Loader. Intriguing - n'est pas?