ORACLE External Table

What is External Table and what are the Advantages of EXTERNAL Table over SQL* Loader?

External Table Functionality provides the ability to access non-Oracle files with ORACLE SQL - we can query them from inside the database using SQL. So, we can just run the validation checks as SELECT statements without the need for a holding table.

Similarly, if we need to do some manipulation of the loaded data it is almost always easier to do this with SQL rather than SQLLDR commands. We can also manage data loads with DBMS_JOB/DBMS_SCHEDULER routines, which further cuts down the need for shell scripts and cron jobs.

The main differences between SQL*Loader and External Tables are:

•   When there are several input datafiles SQL*Loader will generate a bad file and a discard file for each datafile.

•   The CONTINUEIF and CONCATENATE keywords are not supported by External Tables.

•   The GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC are not supported for External Tables.

•   LONG, nested tables, VARRAY, REF, primary key REF, and SID are not supported.

•   For fields in External Tables the character set, decimal separator, date mask and other locale settings are determined by the database NLS settings.

•   The use of the backslash character is allowed for SQL*Loader, but for External Tables this would raise an error. External Tables must use quotation marks instead.

For example:
SQL*Loader
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ""
External Tables
TERMINATED BY ',' ENCLOSED BY "'"

A second driver is available, the ORACLE_DATAPUMP access driver, which uses the Data Pump technology to read the table and unload data to an External Table. This driver allows the user to perform a logical backup that can later be read back to the database without actually loading the data. The ORACLE_DATAPUMP access driver utilizes a proprietary binary format for the external file, so it is not possible to view it as a flat file.

Let's setup the environment

Let's create the demonstration user, and prepare its environment to create an External Table. The example that will be developed first refers to the External Table using the ORACLE_LOADER driver.

Create User EXTTABDEMO
                        Identified by ORACLE
                        Default Tablespace USERS;

Alter user EXTTABDEMO Quota Unlimited on USERS;

GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE, CREATE MATERIALIZED VIEW, ALTER SESSION, CREATE VIEW, CREATE ANY DIRECTORY
To EXTTABDEMO;

The goal is to have ORACLE to refer to below flat file containing

EMP_NO, ENAME, DEPTNO, HIREDATE, SALARY.

The External Table directory is defined inside the database by means of a DIRECTORY object. This object is not validated at creation time, so the user must make sure the physical directory exists and the oracle OS user has read/write privileges on it.

CREATE DIRECTORY EXTTABDIR AS 'F:\External';

Now, the first External Table can be created.

A Basic External Table

Here is the source code of the External Table creation.

SQL> CREATE TABLE EMPLOYEES_EXT
  2  (
  3  EMP_NO NUMBER,
  4  ENAME VARCHAR2(30),
  5  DEPTNO NUMBER,
  6  HIREDATE DATE,
  7  SALARY NUMBER (8,2)
  8  )
  9  ORGANIZATION EXTERNAL
 10  (
 11  TYPE ORACLE_LOADER
 12  DEFAULT Directory EXTTABDIR
 13  Access Parameters
 14  (
 15  Records Delimited By Newline
 16  Fields Terminated By ','
 17  )
 18  Location('EMPLOYEES_DATA.csv')
 19  )
 20  REJECT LIMIT 10;

Table created.

DEFAULT Directory EXTTABDIR: The Directory where the File resides.

RECORDS DELIMITED BY NEWLINE: The New Line Character.

FIELDS TERMINATED BY ',': The Column termination character.

LOCATION('EMPLOYEES_DATA.csv'): The name of the External File.

Once the data is created and all required OS privileges have been properly validated, the data can be seen from inside the database, just as if it were a regular Oracle table.

SQL> SELECT * From EMPLOYEES_EXT;

    EMP_NO ENAME                              DEPTNO HIREDATE      SALARY
---------- ------------------------------ ---------- --------- ----------
      1001 Ravi                                   10 19-AUG-16      48000
      1002 Surya                                  20 26-DEC-16      89000
      1003 Ankit                                  30 12-DEC-16      89000
      1004 Nikhil                                 40 12-DEC-10      77770
      1005 Pritesh                                50 19-AUG-16      48000
      1006 Rajan                                  20 16-AUG-10      77770
      1007 Manu                                   20 16-AUG-10      98888
      1008 Karan                                  20 16-AUG-10      48000

8 rows selected.

This table is read only, so if the user attempts to perform any DML operation against it, it will result in this error:

SQL> Delete From EMPLOYEES_EXT;

Delete From EMPLOYEES_EXT

            *

ERROR at line 1:

ORA-30657: operation not supported on external organized table

As the error message clearly states, this kind of table is only useful for read only operations.

This kind of table doesn't support most of the operations available for regular tables, such as index creation, and statistics gathering, and these types of operations will cause an ORA-30657 error too. The only access method available for External Tables is Full Table Scan, so there is no way to perform a selective data retrieval operation.

The External Tables cannot be recovered, they are just metadata definitions stored in the dictionary tables. The actual data resides in external files, and there is no way to protect them with the regular backup database routines, so it is the user's responsibility to provide proper backup and data management procedures. As the data resides in the external data file, if by any means, it was to be corrupted, altered, or somehow modified, there would be no way to get back the original data.

If the external data file is lost, then this may go unnoticed, until the next SELECT operation takes place.

Unloading Data to External Tables

The driver used to unload data to an External Table is the ORACLE_DATAPUMP access driver. It dumps the contents of a table in a binary proprietary format file. This way you can exchange data with other 10g and higher databases in a preformatted way to meet the other database's requirements. Unloading data to an External Table doesn't make it updateable, the tables are still limited to being read only.

Let's unload the EMPLOYEES table to an External Table:

SQL> Create Table DP_EMPLOYEES
  2  Organization External
  3  (
  4  TYPE Oracle_Datapump
  5  Default Directory EXTTABDIR
  6  Location ('DP_EMPLOYEES.dmp')
  7  )
  8  AS SELECT * From EMPLOYEES;

Table created.

This creates a table named DP_EMPLOYEES, located at the specified EXTTABDIR directory and with a defined OS file name.

SQL> SELECT * From DP_EMPLOYEES;

    EMP_NO ENAME          DEPTNO HIREDATE  DNAME      DLOC           SALARY
---------- ---------- ---------- --------- ---------- ---------- ----------
      1001 Ravi               10 19-AUG-16 ACCOUNTS                   48000
      1002 Surya              20 26-DEC-16 SALES                      89000
      1003 Ankit              30 12-DEC-16 ACCOUNTS                   89000
      1004 Nikhil             40 12-DEC-10 SALES                      77770
      1005 Pritesh            50 19-AUG-16 ACCOUNTS                   48000
      1006 Rajan              20 16-AUG-10 SALES                      77770
      1007 Manu               20 16-AUG-10 BANKING                    98888
      1008 Karan              20 16-AUG-10 BANKING                    48000

8 rows selected.

As we can see, External Tables can serve not only as improvements to the ETL process, but also as a means to manage database environments, and a means of reducing the complexity level of data management from the user's point of view.

Files which were created as a result of above exercise:

  

Limitation of EXTERNAL Tables

No Support for DML:  External Tables are read only so we cannot perform DML operations but the base data can be modified in any text editor.
Poor response for High-Volume Queries: External Tables have a processing overhead, perform full scans, and are not suitable for large tables.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.