External File Data 



	To Access Data From External File / Flat File outside the database

	Read Only (DML Not Possible)

	

-- Created Directory

-- grant Access

-- Keep External Data in a Directory

-- Create External Table 



connect / as sysdba



CREATE OR REPLACE DIRECTORY infy_ext_data_dir AS 'D:\Scripts\EXT_TB_DATA';



GRANT READ, WRITE ON DIRECTORY infy_ext_data_dir TO hr;


CREATE TABLE countries_ext (

  country_code      VARCHAR2(5),

  country_name      VARCHAR2(50),

  country_language  VARCHAR2(50)

)

ORGANIZATION EXTERNAL (

  TYPE ORACLE_LOADER

  DEFAULT DIRECTORY infy_ext_data_dir

  ACCESS PARAMETERS (

    RECORDS DELIMITED BY NEWLINE

    FIELDS TERMINATED BY ','

    MISSING FIELD VALUES ARE NULL

    (

      country_code      CHAR(5),

      country_name      CHAR(50),

      country_language  CHAR(50)

    )

  )

  LOCATION ('Countries1.txt','Countries2.txt')

);





   Oracle_loader: 

		This is used for the reading the flat files using the oracle loader technology. 

		It basically allows to read the files which can be interpreted using SQL loader technology

    

	Oracle_datapump: 

		This is used for both importing and exporting of data using platform independent format

		

	Limitations

		You cannot perform insert, update, and delete operations

		Oracle Indexing not possible

		Constraints not possible

		

	Benefits 

	    a) Queries of external tables complete very quickly even though a full table scan id required with each access

		b) You can join external tables to each other or to standard oracle tables

		c) We can create views on the external table

	

	Unloading

	

	CREATE TABLE countries_ext_tb

	ORGANIZATION EXTERNAL

	(

	TYPE ORACLE_DATAPUMP

	DEFAULT DIRECTORY infy_ext_data_dir

	LOCATION ('countries2.dmp')

	)

	AS SELECT * FROM countries;