Oracle External Table

This is the simplest demonstration of External Table in ORACLE Database.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS

SQL> create user ext_user identified by ext default tablespace users temporary tablespace temp quota unlimited on users;

User created.

SQL> grant connect,resource,create session  to ext_user;

Grant succeeded.

SQL> create or replace directory external as 'F:\khanExternal';

Directory created.

SQL> grant read,write on directory external to exp_full_database,imp_full_database,system;

Grant succeeded.

Create Table Script:

create table ext_student
(
 id number,
 name varchar(255)
)
organization external
(
type oracle_loader
default directory external
access parameters
(
 RECORDS DELIMITED BY NEWLINE
 FIELDS TERMINATED BY ','
)
location('std.txt')
)

std.txt File content:

1,A Rahim Khan
2,A Karim Khan
3,A Rahman Khan
4,Saheda Banu
5,Shahnaj Parveen
6,Mostofa Ali
7,Rokebul Hasan

Run script to create external table and no you can select Data from this Table.

SQL> conn ext_user/ext
Connected.

SQL> create table ext_student
  2  (
  3   id number,
  4   name varchar(255)
  5  )
  6  organization external
  7  (
  8  type oracle_loader
  9  default directory external
 10  access parameters
 11  (
 12   RECORDS DELIMITED BY NEWLINE
 13   FIELDS TERMINATED BY ','
 14  )
 15  location('std.txt')
 16  )
 17  ;

Table created.

SQL> select * from ext_student;

        ID NAME
---------- -------------------------------------
         1 A Rahim Khan
         2 A Karim Khan
         3 A Rahman Khan
         4 Saheda Banu
         5 Shahnaj Parveen
         6 Mostofa Ali
         7 Rokebul Hasan

7 rows selected.

Thanks
A Rahim Khan

2012

05/03

CATEGORY

Oracle DBA

TAGS

Flat File Data Oracle
Oracle Database
Oracle External Table
Oracle User

Write comment