外部表External Table的简单应用[…

External Tables

You canaccess data in external sources as if it were in a table in thedatabase. Oracle allows you read-only access to data in externaltables. External tables are defined as tables that do not reside inthe database, and can be in any format for which an access driveris provided. By providing Oracle with metadata describing anexternal table, Oracle is able to expose the data in the externaltable as if it were data residing in a regular database table. Theexternal data can be queried directly and in parallel usingSQL.

You canSELECT JOIN or SORT external table data. You can also create viewsand synonyms for external tables. However, no DML operations arepossible, and no indexes can be created on externaltables.

The means ofdefining the metadata for external tables is through the CREATETABLE … ORGANIZATION EXTERNAL statement. This external tabledefinition can be thought of as a view that allows running any SQLquery against external data without requiring that the externaldata first be loaded into the database. An access driver is theactual mechanism used to read the external data in thetable.

Oracleprovides an access driver for external tables. It allows thereading of data from external files using the Oracle loadertechnology. The ORACLE_LOADER access driver provides data mappingcapabilities which are a subset of the control file syntax ofSQL*Loader utility.

Oracle’sexternal tables feature provides a valuable means for performingbasic extraction, transformation and transportation tasks that arecommon for data warehousing.

Creating External Tables

You createexternal table using the ORGANIZATION EXTERNAL clause of the CREATETABLE statement. You are not in fact creating a table; that is, anexternal table does not have any extents associated with it.Rather, you are creating metadata in the data dictionary thatenables you to access external data.

The fileempxt1.dat contains the following sample data:

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus

361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper

362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr

363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

 The fileempxt2.dat contains the following sample data:

 

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel

402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega

403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins

404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

 

 Thefollowing SQL statements create an external table in the hr schemanamed

admin_ext_employees and loadits data into the hr.employees table.

 

CONNECT / ASSYSDBA;

-- Set updirectories and grant access to hr

CREATE ORREPLACE DIRECTORY admin_dat_dir

AS'/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/data';

CREATE ORREPLACE DIRECTORY admin_log_dir

AS'/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/log';

CREATE ORREPLACE DIRECTORY admin_bad_dir

AS'/net/dlsun301/private6/examples/submitted/ADMIN/flatfiles/bad';

GRANT READ ONDIRECTORY admin_dat_dir TO hr;

GRANT WRITEON DIRECTORY admin_log_dir TO hr;

GRANT WRITEON DIRECTORY admin_bad_dir TO hr;

 

 

Connect asHR

 

CONNECThr/hr

-- create theexternal table

CREATE TABLEadmin_ext_employees

(employee_idNUMBER(4),

first_nameVARCHAR2(20),

last_nameVARCHAR2(25),

job_idVARCHAR2(10),

manager_idNUMBER(4),

hire_dateDATE,

salaryNUMBER(8,2),

commission_pctNUMBER(2,2),

department_idNUMBER(4),

emailVARCHAR2(25)

)

ORGANIZATIONEXTERNAL

(

TYPEORACLE_LOADER

DEFAULTDIRECTORY admin_dat_dir

ACCESSPARAMETERS

(

recordsdelimited by newline

badfileadmin_bad_dir:'empxt%a_%p.bad'

logfileadmin_log_dir:'empxt%a_%p.log'

fieldsterminated by ','

missing fieldvalues are null

(employee_id, first_name, last_name, job_id, manager_id,

hire_datechar date_format date mask "dd-mon-yyyy",

salary,commission_pct, department_id, email

)

)

LOCATION('empxt1.dat', 'empxt2.dat')

)

PARALLEL

REJECT LIMITUNLIMITED;

-- enableparallel for loading (good if lots of data to load)

ALTER SESSIONENABLE PARALLEL DML;

-- load thedata in hr employees table

INSERT INTOemployees (employee_id, first_name, last_name, job_id,manager_id,

hire_date,salary, commission_pct, department_id, email)

SELECT * FROMadmin_ext_employees;