External Table feature of Oracle 10g on Windows

Using External Table feature of Oracle 10g on Windows:

External Tables are used for dataloading to oracle 10g Database.

1. Create a Directory C:\wkdir

2. connect to sqlplus as system or sysdba

3. Execute the below statement
create or replace directory ext_tab_dir as 'c:\wkdir';
grant read,write on directory ext_tab_dir to scott;

Here scott is the schema from where you will be creating external table
and loading the data to it from the flat file located in 'c:\wkdir' directory.

4. disconnect from sqlplus

5. connect to sqlplus as scott

6. execute the below statements

drop table order_items_ext; --If this table already exists with some other columns.

create table order_items_ext
( order_id number(12)
, line_id NUMBER(3)
, product_id number(6)
, unit_price number(8,2)
, quantity number(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('order_items.dat')
)
REJECT LIMIT UNLIMITED;

for our testing we can create order_items.dat file with below records:
123,12,465,14.50,2
124,13,466,15.50,5

The file order_items.dat should be placed in c:\wkdir directory.

7. You will alter the table to set the PROJECT COLUMN attribute to ALL. This is the default.
Then you can query the external table. Execute the following command:

alter table order_items_ext project column all;
select count(order_id)from order_items_ext;
select * from order_items_ext;

8. If you find some records missing then you can check the below files in the 'c:\wkdir'
ORDER_ITEMS_EXT_#####.log
ORDER_ITEMS_EXT_#####.bad

Cheers,

Khwaja