what is External tables? explain with examples
Answers were Sorted based on User's Feedback
Answer / jayaprakash gutti
External table is a complement to SQl* Loader.
External table is more faster compared to SQL loader when
there is thousands of records in source data file.
When we create External table, a link is established between
external table and source data file (which is located in the
file system/Oracle Server).
whenever we query external table like:
select * from external_table;
it fetches data from the file and displayed.
also, external tables are read-only.
you can't perform DML operations.
we can insert the data in the external table to any of the
data base table we want.
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / 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
Is This Answer Correct ? | 2 Yes | 2 No |
How do you delete a table?
How to change the order of columns in Oracle SQL Plus ?
select * from emp where sal in(select max(sal) from emp) here there is any limit for in operator how many values accpect ?
Explain aggregate functions are available there in sql?
What is bulk collect in pl sql?
How do you declare a user-defined exception?
What does pl sql developer do?
How subquery works in sql?
What SQL keyword must immediately follow the UNION ALL statement? 1. SELECT 2. INTO 3. ORDER 4. WHERE 5. JOIN
What is a database link?
Is join and inner join the same?
What is nosql vs sql?