| |
 |
|
Oracle Tips by Burleson |
Chapter 6 Oracle Table Design
Figure 6.1 - Oracle read and write interfaces to
OS files
As you can see, Oracle lets a database program
write to flat files using the utl_file utility. Combined with
external table’s read ability, this new topology removes the
requirement that all Oracle data reside inside Oracle tables,
opening new applications for Oracle. Let’s take a closer look at how
this feature works.
Defining an external table
Let’s say you want Oracle to refer to this comma-delimited flat
file:
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
The file contains the following employee information:
-
Employee ID
-
Last name
-
Job description
-
Manager’s employee ID
-
Hire date
-
Salary
-
Commission
-
Department
So, how do we define this file to Oracle? First, we must create an
Oracle directory entry in the data dictionary that points to the
Windows directory where the flat file resides. In this example,
we’ll name the directory testdir and point it to c:\docs\pubsdb\queries:
SQL> create directory testdir as
‘c:\docs\pubsdb\queries’;
Directory Created.
Now that we have the directory, we can define the structure of the
external file to Oracle. You’ll see this code in Listing A.
|
create table
emp_ext
(
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 testdir
access parameters
(
records delimited by newline
fields terminated by ‘,’
)
location (‘emp_ext.csv’)
)
reject limit 1000; |
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
