Answer Posted / karkuvelrajan muthusamy

External tables can be used to fetch data from a physical
data file present in the OS path of the database server.. It
is similar to SQL*Loader where oracle reads data from a
datafile and loads into oracle tables.. An external table is
very similar.. Whenever we create an eternal table, oracle
doesnot load data from file instead a table that referes to
the file only is created.. A sample syntax could be as
following:

create table ext_tbl
(e_name varchar2(50),
e_sal number)
ORGANIZATION EXTERNAL(
TYPE ORACLE_LOADER
-- Default->ORACLE_LOADER, Oracle_datapump can also be used
DEFAULT DIRECTORY ext_dir
-- An oracle directory has to be created with
-- name ext_dir
ACCESS PARAMETERS
(
FIELDS TERMINATED BY ','
RECORDS DELIMITED BY NEWLINE
MISSING FIELD VALUES ARE NULL
(e_name, e_sal)
)
LOCATION('file1.txt')
)

The file1.txt should be in the following format:
Kumar,20000
Raj,10000
Patel,34000

Hope this helps.

Regards,
Karkuvelrajan M

What are the different types of a subquery?

583

---

Is a table valued function object?

648

---

Can %notfound return null after a fetch?

652

---

What is cte sql?

586

---

Why are cursors used?

653

---

---

What is sql profiler in oracle?

635

---

Why is nosql good?

662

---

How do you optimize a stored procedure in sql?

558

---

what is the use of set statement in tsql? : Transact sql

598

---

Can there be more than one function with a similar name in a pl/sql block?

617

---

What is percent sign in sql?

797

---

what is a table called, if it has neither cluster nor non-cluster index? What is it used for? : Sql dba

589

---

what are the drivers in mysql? : Sql dba

617

---

How do I run a program in pl sql?

561

---

How does sql*loader handles newline characters in a record? : aql loader

645

---