Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Feb 7, 2003

External Tables in Oracle 9i

By DatabaseJournal.com Staff

Ajay Gursahani

This article gives a brief understanding about External tables. External Tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. This external table definition can be thought of as a view that allows running any SQL query against external data without requiring that the external data first be loaded into the database.

You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and indexes cannot be created on external tables.

Oracle provides the means of defining the metadata for external tables through the CREATE TABLE ... ORGANIZATION EXTERNAL statement.

Before firing the above command we need to create a directory object where the external files will reside.

CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\EXT_TABLES\';

Example: The example below describes how to create external files, create external tables, query external tables and create views.

Step I: Creating the flat files, which will be queried

The file "emp_ext1.dat" contains the following sample data:

101,Andy,FINANCE,15-DEC-1995
102,Jack,HRD,01-MAY-1996
103,Rob,DEVELOPMENT,01-JUN-1996
104,Joe,DEVELOPMENT,01-JUN-1996

The file "emp_ext2.dat" contains the following sample data:

105,Maggie,FINANCE,15-DEC-1997
106,Russell,HRD,01-MAY-1998
107,Katie,DEVELOPMENT,01-JUN-1998
108,Jay,DEVELOPMENT,01-JUN-1998

Copy these files under "C:\EXT_TABLES"

Step II: Create a Directory Object where the flat files will reside

SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\EXT_TABLES';

Directory created.

Step III: Create metadata for the external table

SQL> CREATE TABLE emp_ext
	(
	empcode NUMBER(4), 
	empname VARCHAR2(25),
	deptname VARCHAR2(25),
	hiredate date
	)
  ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_tables
      ACCESS PARAMETERS
      (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
      )
      LOCATION ('emp_ext1.dat','emp_ext2.dat')
    )
  REJECT LIMIT UNLIMITED;

Table created.

The REJECT LIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data.

"The ORACLE_LOADER is an access driver for loading data from the external files into the tables."

Step IV: Querying Data

SQL> SELECT * FROM  emp_ext;

        EMPCODE	 EMPNAME              DEPTNAME               HIREDATE
       --------- ------------------- ---------------------- ---------
         101 Andy                       FINANCE                15-DEC-95
         102 Jack                       HRD                    01-MAY-96
         103 Rob                        DEVELOPMENT            01-JUN-96
         104 Joe                        DEVELOPMENT            01-JUN-96
         105 Maggie                     FINANCE                15-DEC-97
         106 Russell                    HRD                    01-MAY-98
         107 Katie                      DEVELOPMENT            01-JUN-98
         108 Jay                        DEVELOPMENT            01-JUN-98

8 rows selected.

Step V: Creating Views

SQL> CREATE VIEW v_empext_dev AS
		SELECT * FROM emp_ext
		WHERE deptname='DEVELOPMENT';
View created.

SQL> SELECT * FROM v_empext_dev;

         EMPCODE EMPNAME            DEPTNAME             HIREDATE
       ------------ ------------- ---------------------- ---------
         103 Rob                     DEVELOPMENT       01-JUN-96
         104 Joe                     DEVELOPMENT       01-JUN-96
         107 Katie                   DEVELOPMENT       01-JUN-98
         108 Jay                     DEVELOPMENT       01-JUN-98

You can get the information of the objects you have created through DBA_OBJECTS, ALL_OBJECTS or USER_OBJECTS.

SQL> 	SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'EMP_EXT';

OBJECT_NAME                 OBJECT_TYPE
---------------------- 	------------------
EMP_EXT		          TABLE

1 row selected.

SQL> 	SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'EXT_TABLES';

OBJECT_NAME                 OBJECT_TYPE
---------------------- 	------------------
EXT_TABLES                  DIRECTORY

1 row selected.

Populating Tables using the INSERT command

You can populate data from external files using an "insert into ... select from" statement instead of using SQL*Loader. This method provides very fast data loads.

Example:

Consider a table EMPLOYEES:

SQL> desc EMPLOYEES;

 Name                              Null?    	Type
 --------------------------------- -------- 	--------------

 EMPCODE                                       NUMBER(4)
 EMPNAME                                       VARCHAR2(25)
 DEPTNAME                                      VARCHAR2(25)
 HIREDATE                                      DATE

SQL> INSERT INTO employees 
     (empcode,empname,deptname,hiredate) SELECT * FROM emp_ext;

8 rows created.

SQL> SELECT * FROM employees;

        EMPCODE 	EMPNAME               DEPTNAME               HIREDATE
       ------------ ------------------- ---------------------- ---------
         101 Andy                        FINANCE                 15-DEC-95
         102 Jack                        HRD                     01-MAY-96
         103 Rob                         DEVELOPMENT             01-JUN-96
         104 Joe                         DEVELOPMENT             01-JUN-96
         105 Maggie                      FINANCE                 15-DEC-97
         106 Russell                     HRD                     01-MAY-98
         107 Katie                       DEVELOPMENT             01-JUN-98
         108 Jay                         DEVELOPMENT             01-JUN-98

8 rows selected.

Dropping External Tables

For an external table, the DROP TABLE statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.

Summary

The external files are thus tables in the data dictionary, which can be queried as you would query ordinary Oracle tables. You can perform fast data loads using the above method instead of using SQL*Loader.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date