Re: External Table Help

From: michael ngong <mngong_at_yahoo.com>
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

Original text of this message