Completed the Configuring Linux for the Installation of Oracle Database 10g lesson.

Completed the Installing the Oracle Database 10g on Linux lesson.

Completed the Postinstallation Tasks lesson.

Downloaded and unzipped exttable.zip into your working directory (i.e. /home/oracle/wkdir).

Created a directory for this lesson. Open a terminal window and execute the following:

cd wkdir
sqlplus /nolog
connect / as sysdba
create or replace directory ext_tab_dir as '/home/oracle/wkdir';
grant read, write on directory ext_tab_dir to oe;

Create an external table that will unload data into two files. From your terminal window, execute the following commands:

connect oe/oe
@crtabl01

The query in the crtabl01.sql script is as follows:

DROP TABLE emp_ext;

CREATE TABLE emp_ext
  (first_name, last_name, department_name)
   ORGANIZATION EXTERNAL
    (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY ext_tab_dir
     LOCATION
      ('emp1.exp','emp2.exp')
    )
   PARALLEL
AS
SELECT e.first_name
,      e.last_name
,      d.department_name
FROM   hr.employees   e JOIN
       hr.departments d USING (department_id)
WHERE  d.department_name
       in ('Marketing', 'Purchasing');

Note that you are using ORACLE_DATAPUMP as the type.

Now you can query the external table by executing the following script from your terminal window:

@query01

The query in the query01.sql script is as follows:

select * from emp_ext;
exit

Note that the external table shows two employees in Marketing and six employees in Purchasing.

Review the contents of the order_items.dat external data file. From your terminal window, execute the following command:

more order_items.dat

Note the value 2000 in row 3, which is 4 characters. This may cause some trouble.

Now you can create the external table by executing the following commands:

sqlplus oe/oe
@crtabl02

The command in the crtabl02.sql script is as follows:

drop table order_items_ext;

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;

Note that the format of LINE_ID is NUMBER(3).

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; 

Two rows were found.

To see why only two rows were found when there are three rows in the order_items.dat file, you should look at the Loader log file. Open ORDER_ITEMS_EXT_#####.log from gedit.

Note that the record that had a LINE_ID greater than 3 numbers was rejected when the external table was accessed. That row never reached the SQL query processing.

To see the row that was rejected, open ORDER_ITEMS_EXT_#####.bad from gedit.

Now you can change the PROJECT COLUMN attribute to REFERENCED and run the same query to see what happens. Execute the following command:

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

Three rows were found. This query projected the external data to the field needed (ORDER_ID) so all three records were accepted.