Creating External Tables in Oracle

Creating External Tables in Oracle

Create Database Directories as sys user

CREATE OR REPLACE DIRECTORY ext_table AS '/u01/app/oracle/ext_tables/tables';
GRANT READ, WRITE ON DIRECTORY ext_table TO HR;

CREATE OR REPLACE DIRECTORY extab_bad_dir AS '/u01/app/oracle/ext_tables/bad_files';
GRANT READ, WRITE ON DIRECTORY extab_bad_dir TO HR;

CREATE OR REPLACE DIRECTORY extab_log_dir AS '/u01/app/oracle/ext_tables/log_files';
GRANT READ, WRITE ON DIRECTORY extab_log_dir TO HR;



Create the file empxt1.dat containing the following sample data in /u01/app/oracle/ext_tables/tables:

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda


Create the file empxt2.dat containing the following sample data in /u01/app/oracle/ext_tables/tables:

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

Connect to HR user.

Create the external table using the method ORACLE_LOADER.

CREATE TABLE admin_ext_employees
                   (employee_id       NUMBER(4),
                    first_name        VARCHAR2(20),
                    last_name         VARCHAR2(25),
                    job_id            VARCHAR2(10),
                    manager_id        NUMBER(4),
                    hire_date         DATE,
                    salary            NUMBER(8,2),
                    commission_pct    NUMBER(2,2),
                    department_id     NUMBER(4),
                    email             VARCHAR2(25)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY ext_table
       ACCESS PARAMETERS
       (
         records delimited by newline
         badfile extab_bad_dir:'empxt%a_%p.bad'
         logfile extab_log_dir:'empxt%a_%p.log'
         fields terminated by ','
         missing field values are null
         ( employee_id, first_name, last_name, job_id, manager_id,
           hire_date char date_format date mask "dd-mon-yyyy",
           salary, commission_pct, department_id, email
         )
       )
       LOCATION ('empxt1.dat', 'empxt2.dat')
     )
     PARALLEL
     REJECT LIMIT UNLIMITED;
-- enable parallel for loading (good if lots of data to load)
ALTER SESSION ENABLE PARALLEL DML;

-- load the data from the external table intohr employees table.

INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,
                       hire_date, salary, commission_pct, department_id, email)
            SELECT * FROM admin_ext_employees;

---Check the data in the external table admin_ext_employees.

Select * from admin_ext_employees;


Create the external table using the method ORACLE_DATAPUMP.

CREATE TABLE ext_emp_query_results
(first_name, last_name, department_name)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXT_TABLE
LOCATION ('emp1.exp','emp2.exp','emp3.exp')
)
PARALLEL
AS
SELECT e.first_name,e.last_name,d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND
d.department_name in
('Marketing', 'Purchasing');

Check the values in the external table.

select * from ext_emp_query_results;