Tutorials to .com

Tutorials to .com » Database » Oracle » Access non-Oracle files to create the external table

Access non-Oracle files to create the external table

Print View , by: iSee ,Total views: 20 ,Word Count: 589 ,Date: Tue, 25 Aug 2009 Time: 9:59 AM

Oracle9i can directly obtain data from the operating system files, and let it look like the same as the database's internal tables.

Oracle9i has expanded its interface with the operating system, so let any type of flat file to operate as if this file is the same as a relational table, which in fact allows you to write any type of SQL to access the standard relational table. In fact, you can even use Microsoft Excel spreadsheet files (. Xls files), so that they look like the same form within Oracle9i.

This (use) an external table functionality is often metadata is particularly useful for oracle data warehouse. You do not have to spend time using the Oracle SQL * Loader tool to put the data into the database, but can put the information stay in the flat file, and for the flat file to create an external table.

Use of this technology, and information as if becomes a part of Oracle database, and in fact, the information outside of the Oracle.

Now let's look at a simple example. We will be a comma-delimited flat file start as follows.

7369, SMITH, CLERK ,7902,12-JAN-98, 800,20

7499, ALLEN, SALESMAN ,7698,03-MAR-96, 1600,300,30

7521, WARD, SALESMAN ,7698,27-APR-97, 1250,500,30

7566, JONES, MANAGER ,7839,20-OCT-97, 2975,, 20

7654, MARTIN, SALESMAN ,7698,28-SEP-98, 1250,1400,30

7698, BLAKE, MANAGER ,7839,11-NOV-98, 2850,, 30

7782, CLARK, MANAGER ,7839,29-DEC-97, 2450,, 10

7788, SCOTT, ANALYST ,7566,21-SEP-96, 3000,, 20

7839, KING, PRESIDENT, ,27-MAY-98, 5000,, 10

7844, TURNER, SALESMAN ,7698,24-OCT-98, 1500,0,30

7876, ADAMS, CLERK ,7788,12-JUN-97, 1100,, 20

Use the following command, we can put this file is defined as the Oracle form.

Create directory blah as '/ home4/teach17'

create table external_emp (

EMPNO NUMBER (4),

ENAME VARCHAR2 (10),

JOB VARCHAR2 (9),

MGR NUMBER (4),

HIREDATE DATE,

SAL NUMBER (7,2),

COMM NUMBER (7,2),

DEPTNO NUMBER (2))

Organization external

(type oracle_loader

default directory BLAH

access parameters (records delimited by newline

fields terminated by ',')

location ( 'extemp.txt'))

reject limit 1000;

Now, we can use any SQL in this table. However, the external forms there are some very important limitations: you can not use DML operations can not create an index of external table. Moreover, the external forms dealing with delays, so they are not suitable for large-scale form.

The author: Donald Burleson to do a database administrator has been 23 years, and has written 14 books on the database, and more than 100 articles. He is "Oracle insider (Oracle Internals)" editor, and operates Burleson Oracle Consulting (Burleson Oracle Consulting).

(Editor: Ming Ming mingming_ky@126.com TEL: (010) 68476606)




Oracle Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.