EXTERNAL TABLES

An external table is a database file or even a text file on local disk which MUST BE :

1- column types in excel or word files match column types of the database table .

i.e: if first column in database table is NUMBER , first column in excel file should be NUMBER TOO !


2- character length must match too .

i.e: if first column in database table is NAME and accepts only 4 characters to be inserted , first column in excel file is NAME and contains value 'Ahmed' which is 5 characters length ... this causes an error when you try to select data from external table.


3- READ , WRITE on directory .. these privileges must be granted to user creating an external table.

-- EXTERNAL TABLES ARE USEFUL TO IMPORT YOUR MS EXCEL FILES OR DATA STORED IN ANY FILE TO YOUR DATABASE !!

EXAMPLE :

CREATE TABLE TEST1
(TEST NUMBER(3) , NAME VARCHAR2(25), LOCATION VARCHAR2(25))

ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER
DEFAULT DIRECTORY MYDIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY newline
NOLOGFILE
NOBADFILE
FIELDS TERMINATED BY ',' )
LOCATION ( 'TEXTFILE.TXT)) ;

EASY !!