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

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: