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;
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;
No comments:
Post a Comment