Re: External Table Help
Date: 29 May 2003 07:11:05 -0700
Message-ID: <ecf365d5.0305290611.7b6ed282_at_posting.google.com>
lefebvre_at_iwavesolutions.com (Marc A. Lefebvre US-775) wrote in message news:<5ef1a86c.0305282234.350d5b54_at_posting.google.com>...
> I want to use the CREATE TABLE statement and use the external table
> clause to point to a flat file. My question is, does this flat file
> specified as the data source of the external table have to be local to
> the database or can it reside on another server in the network? How
> do you specify this in the syntax of the CREATE statement?
>
> Thanks!
>
> Marc
There is documentation at tahiti.oracle.com which should give you a much greater indepth than what I will try to do here anyway....... Since I see no answers yet I will briefly put in my one cent worth about external tables
To be able to create an external table you need a flat file(s) (of course) which contains the data you will use to populate your database table
You need to create a directory in your database which should point to where the flat file(s) reside
sql>Create or replace directory extab as 'c:\exdir\' Directory created
Remember that external tables are read only from the
database,insert,update delete are not supported nor can external
tables be indexed.
Let your flat file be
mike ,38, 1964
ern , 36 , 1966
You will have to create a table which can hold those variables
create table externalage(name varchar2(23),age number,BirthYear
number)
ORGANIZATION EXTERNAL
(type ORACLE_LOADER
default directory extab
(ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
fields terminated by ','
MISSING FIELD VALUES ARE NULL
(name,age,BirthYear))
LOCATION('specs.txt')
REJECT LIMIT UNLIMITED
Organization external means you are using external tables
Type is the access driver you will use (ORACLE_LOADER is the default )
You can also define a badfile and a logfile just like in Oracle loader
and you could use other loading features provided by sql loader as
well.
The file(specs.txt) will be sought for in your directory defined at
the start
You can drop the table from within a database and not the flat file
YOu can create a table as select from your external table and then go
ahead to index your new table if need arises.
As to wether this can be done from elsewhere in your network I have
read that if you set utl_file_dir=\\remoteComputer........ you will
have it to work though Oracle warns about it.Not tried this yet
HTH Michael Tubuo Ngong Received on Thu May 29 2003 - 16:11:05 CEST