Using External table

EXTERNAL TABLE or STAGING TABLE (to load csv files into staging table)

You can user external table feature to access external files as if they are tables inside the database.

When you create an external table, you define its structure and location within oracle.

When you query the table, oracle reads the external table and returns the results just as if the data had been stored within the database.

ACCESSING EXTERNAL TABLE DATA

To access external files from within oracle, you must first use the create directory command to define a directory object pointing to the external file location

Users who will access the external files must have the read and write privilege on the directory.

Create user defined directory

Grant create any directory to scott;

GRANT READ, WRITE ON DIRECTORY REDDYS_DIR TO SCOTT;

SQL>Create directory REDDYS_dir as 'C:\ORA FILES';  /* directory on the Oracle database server */

Directory created.

CREATE EXTERNAL TABLE (whenever client put the data into directory external table takes the data automatically)

  SQL> Create table dept_ext

  2           (deptno number(2),

  3           Dname varchar(14),

  4           Loc varchar(13))

  5           Organization external  ( type oracle_loader

  6                                                   Default directory REDDYS_dir

  7                                                   Access parameters

  8                                                   ( records delimited by newline

  9                                                      Fields terminated by ","

 10                                                      ( deptno number(2),

 11                                                        Dname varchar(14),

 12                                                        Loc varchar(13)))

 13*          Location ('C:\ORA FILES\DEPT.LST'));

Table created.

We can create the procedure using UTL_FILES for Moving data from external table into oracle database.

SELECTING DATA FROM EXTERNAL TABLE

SQL> select * from dept_ext;

This will read from dept.lst which is a operating system level file.

LIMITATIONS ON EXTERNAL TABLES

a)   You cannot perform insert, update, and delete operations

b)   Indexing not possible

c)   Constraints not possible

BENEFITS OF EXTERNAL TABLE

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 tables