/*********************************************************************
UNLOAD -> Populate external table via normal table
**********************************************************************/
create table ext_product
(
PRODUCTID,
DESCRIPTION,
PRODUCTNAME
)
organization external
(
type oracle_datapump
default directory dir1
location ('product.dat')
)
as
select PRODUCTID,
DESCRIPTION,
PRODUCTNAME
from product;
/*********************************************************************
Create external table base on existing dump file
**********************************************************************/
create table ext_product_2
(
PRODUCTID number(10),
DESCRIPTION varchar2(500),
PRODUCTNAME varchar2(30)
)
organization external
(
type oracle_datapump
default directory dir1
location ('product.dat')
)
/
/*********************************************************************
SQL-LOADER EXAMPLE
**********************************************************************/
create table ext_product_3
(
PRODUCTID number(10),
DESCRIPTION varchar2(500),
PRODUCTNAME varchar2(30)
)
organization external
(
type oracle_loader
default directory dir1
access parameters ( records delimited by newline fields terminated by ',')
location ('product3.dat')
)
reject limit unlimited
/
/*********************************************************************
Projected columns examples
**********************************************************************/
create table pro1
(
ID number(10),
S_ID number(10),
NAME varchar2(30)
)
organization external
(
type oracle_loader
default directory dir1
access parameters ( records delimited by newline fields terminated by ',')
location ('pro1.dat')
)
reject limit unlimited
/
/****** Pro1 data **************
100,1,Hardisk 7200A
101,2,Hardisk 7200B
102,3,Hardisk 7200C
103,11111111111111111111111,Hardisk 7200D
SQL> select * from pro1;
ID S_ID NAME
---------- ---------- ----------------------------
100 1 Hardisk 7200A
101 2 Hardisk 7200B
102 3 Hardisk 7200C
SQL> select count(id) from pro1;
COUNT(ID)
----------
3
SQL> select count(s_id) from pro1;
COUNT(S_ID)
-----------
3
SQL> alter table pro1 project column referenced;
Table altered.
SQL> select count(id) from pro1;
COUNT(ID)
----------
4
SQL> select count(id),name from pro1
2 group by name;
COUNT(ID) NAME
---------- ------------------------------
1 Hardisk 7200B
1 Hardisk 7200A
1 Hardisk 7200C
1 Hardisk 7200D
SQL> select count(s_id) from pro1;
COUNT(S_ID)
-----------
3
SQL>
