KEMBAR78
Oracle External Tables Guide | PDF | Oracle Database | Sql
Scribd
Upload
343 views2 pages

Oracle External Tables Guide

External tables allow Oracle to access data stored outside the database as if it were in a regular database table. The data can be queried directly using SQL and processed in parallel. While queries are supported, DML operations like update, insert and delete are not possible on external tables. External tables use an access driver and metadata in the CREATE TABLE statement to read external data files. Oracle provides the ORACLE_LOADER driver to read data from external files using Oracle loader functionality. The document provides the syntax for creating an external table to load data from an operating system file into an Oracle table.

Uploaded by

Biswajit Das
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
343 views2 pages

Oracle External Tables Guide

External tables allow Oracle to access data stored outside the database as if it were in a regular database table. The data can be queried directly using SQL and processed in parallel. While queries are supported, DML operations like update, insert and delete are not possible on external tables. External tables use an access driver and metadata in the CREATE TABLE statement to read external data files. Oracle provides the ORACLE_LOADER driver to read data from external files using Oracle loader functionality. The document provides the syntax for creating an external table to load data from an operating system file into an Oracle table.

Uploaded by

Biswajit Das
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 2

ORACLE 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. By providing Oracle with metadata describing an external table, Oracle is able to expose the data in the external table as if it were data residing in a regular database table. The external data can be queried directly and in parallel using SQL.One can select,Join or Sort External Table data.One can create Views and synonyms for external Tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables. The metadata for external tables is done through the CREATE TABLE ... ORGANIZATION EXTERNAL statement. An access driver is the mechanism used to read the external data in the table. Oracle provides an access driver for external tables. It allows the reading of data from external files using the Oracle loader technology. The ORACLE_LOADER access driver provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility. Oracle's external tables feature provides a valuable means for performing basic extraction, transformation, and transportation (ETT) tasks that are common for datawarehousing applications. Lets see the Syntax for creating External Tables from Operating System file. Assume You got the file say EmpETL.DAT in Direcotry C:\Oracle with following data.
7369,SMITH,CLERK , 7902 17-DEC-80,800,200,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,200,30 7521,WARD,SALESMAN,7698,22-FEB-81,1250,200,30 7566, JONES,MANAGER ,7839,02-APR-81,2975,200,20 7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,200,30 The following SQL statements create an external table and load its data into database table TEMP of User Scott.

SQL>Connect system/manager Connected SQL> CREATE OR REPLACE DIRECTORY DATA_DIR AS 'C:\Oracle Directory created. SQL>GRANT READ ON DIRECTORY DATA_DIR TO scott; Grant succeeded SQL>CONNECT SCOTT/TIGER CREATE TABLE EMPETL (empno NUMBER(4), ename VARCHAR2(20),

job VARCHAR2(20), mgr NUMBER(4), hiredate DATE, sal NUMBER(8,2), comm NUMBER(8,2), deptno NUMBER(2) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( fields terminated by ',' missing field values are null (empno, ename, job, mgr, hiredate char date_format date mask 'dd.mm.yyyy', sal, comm, deptno ) ) LOCATION (EmpETL.DAT') ) PARALLEL REJECT LIMIT UNLIMITED;

Now Load the External Table data into some Oracle user table as follows
SQL>create table TEMP as select * from EMPETL;

Normally You will encounter erros If the wrong file is specified in Create Table statement when you are reading data from External table .External Tables are alternative for the SQL*Loader Utility and very handy Tehnique for ETL Process in Datawarehousing Projects.

You might also like