Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » oracle9i external table
oracle9i external table [message #76359] Mon, 01 April 2002 23:28 Go to next message
began
Messages: 1
Registered: April 2002
Junior Member
iam having a file in ms-word document now i need to create table using external table and store the data which is in the word document.
Re: oracle9i external table [message #76364 is a reply to message #76359] Tue, 02 April 2002 03:08 Go to previous messageGo to next message
Ardian B. Santoso
Messages: 11
Registered: April 2002
Junior Member
You can read this article from Oracle Support
-------------------------------------------------

PURPOSE
-------

This note explains the purpose and usage of external tables.
External tables appear with version 9i of Oracle.

SCOPE & APPLICATION
-------------------

For DBAs who need to store data outside the database and manipulate them inside
the database without requiring to load the data in the database.

This feature becomes interesting in a data warehouse environment during the ETL
process (Extraction Transformation Loading), as a complement to SQL*Loader: it
prevents temporary tables to be created during the Extraction and Transformation
phases, thus reducing space allocated and risk of abortion during the whole
process. It can be used in place of SQL*Loader when the external data volume is
large and used only once.

What are External Tables
------------------------
External tables are like regular SQL tables with some exceptions:

o The metadata of external tables is created using the SQL "CREATE TABLE ... ORGANIZATION EXTERNAL" statement.

o The data resides outside the database in OS files, thus the EXTERNAL organization.

o The OS files are identified inside the database through a logical directory defining the OS physical directory where they are located.

o The data is read only.

o You cannot perform any DML operations, nor create indexes.

o The external table can be queried and joined directly, in parallel using the SQL statement SELECT.

Example
-------

********************
A. Create the external table to extract the data without loading them inside the database

*****************

To create an external table from 2 flat files, perform the following steps:
-------------------
1/ Create flat files emp1.dat and emp2.dat
2/ Create a directory that defines the location of the directory where the flat files reside on the OS
3/ Create the external table (metadata)
4/ Select data from the external table to verify that data are visible
-------------------

1/ Create emp1.dat and emp2.dat flat files:

$ vi /u01/ora9i/data/emp1.dat
7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
7566,JONES,MANAGER,7839,02-APR-81,1150,0,20

$vi /u01/ora9i/data/emp2.dat
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30
7698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30
7934,MILLER,CLERK,7782,23-JAN-82,3500,0,10

Check OS level permissions to the user for read / write to this directory.

2/ Create a logical directory to map the OS directory where the external files reside and grant permissions to SCOTT :

SQL> create directory emp_dir as '/u01/ora9i/data' ;

Directory created.

SQL> GRANT READ ON DIRECTORY emp_dir TO scott;
Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY emp_dir TO scott;
Grant succeeded.

3/ Create the external table :

SQL> create table scott.emp_ext
(emp_id number(4), ename varchar2(12),
job varchar2(12) , mgr_id number(4) ,
hiredate date, salary number(8),
comm number(8),
dept_id number(2))
organization external
(type oracle_loader
default directory emp_dir
access parameters (records delimited by newline
fields terminated by ',')
location ('emp1.dat','emp2.dat'));

Table created.

4/ Check the data retrieval:

SQL> select * from scott.emp_ext;

EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
------ ------- ---------- ------- --------- -------- ---------- -------
7369 SMITH CLERK 7902 17-DEC-80 100 0 20
7499 ALLEN SALESMAN 7698 20-FEB-81 250 0 30
7521 WARD SALESMAN 7698 22-FEB-81 450 0 30
7566 JONES MANAGER 7839 02-APR-81 1150 0 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30
7698 BLAKE MANAGER 7839 01-MAY-81 1550 0 30
7934 MILLER CLERK 7782 23-JAN-82 3500 0 10

7 rows selected.

If you need to get the external tables names and characteristics:

SQL> select owner, table_name, default_directory_name, ACCESS_PARAMETERS
2 from DBA_EXTERNAL_TABLES ;

OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_PARAMETERS
------ ----------- ---------------------- -----------------
SCOTT EMP_EXT EMP_DIR records delimited by newline
fields terminated by ','

If you need to retrieve the locations of the flat files, use the following view:

SQL> select * from DBA_EXTERNAL_LOCATIONS;

OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME
--------- ---------- -------------------------- --- --------------------
SCOTT EMP_EXT emp1.dat SYS EMP_DIR
SCOTT EMP_EXT emp2.dat SYS EMP_DIR

*** ************************************************************************
*** B. Create a table function to transform the data
*** ************************************************************************

SQL> create or replace type rec_emp_type is object
2 (
3 emp_id number(4),
4 ename varchar2(12) ,
5 job varchar2(12) ,
6 mgr_id number(4) ,
7 hiredate date,
8 salary number(8),
9 comm number(8),
10 dept_id number(2)
11 );
12 /
Type created.

SQL> create or replace type table_emp_type is table of rec_emp_type;
2 /
Type created.

SQL> create or replace function transform
2 return table_emp_type PIPELINED is
3 begin
4 for query in (select * from scott.emp_ext) loop
5 -- Simple transformation but should be complex !
6 query.salary := query.salary*1.2;
7 pipe row (query);
8 end loop;
9 return;
10 end;
11 /
Function created.

SQL> select * from table(transform);

EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
------ ------- ---------- ------- --------- -------- ---------- -------
7369 SMITH CLERK 7902 17-DEC-80 120 0 20
7499 ALLEN SALESMAN 7698 20-FEB-81 300 0 30
7521 WARD SALESMAN 7698 22-FEB-81 540 0 30
7566 JONES MANAGER 7839 02-APR-81 1380 0 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1500 0 30
7698 BLAKE MANAGER 7839 01-MAY-81 1860 0 30
7934 MILLER CLERK 7782 23-JAN-82 4200 0 10

7 rows selected.

*** ************************************************************************
*** C. Load the transformed data retrieved from the external table into the
*** database table
*** ************************************************************************

SQL> select * from scott.employees order by empno;

EMPNO FIRST_NAME JOB MANAGER_ID HIRE_DATE SALARY COMM DEPARTMENT_ID
------ ---------- --------- ---------- --------- ------ ---- -------------
7499 ALLEN SALESMAN 7698 20-FEB-81 250 0 30
7521 WARD SALESMAN 7698 22-FEB-81 450 0 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30
7566 JONES MANAGER 7839 02-APR-81 1150 0 20
7698 BLAKE MANAGER 7839 01-MAY-81 1550 0 30

SQL> MERGE INTO scott.employees i
2 USING
3 (select *
4 from table(transform)) t
5 ON (i.empno = t.emp_id)
6 WHEN MATCHED THEN
7 UPDATE SET i.salary = t.salary
8 WHEN NOT MATCHED THEN
9 INSERT (empno, FIRST_NAME, JOB, MANAGER_ID, HIRE_DATE,
10 SALARY, COMMISSION_PCT , DEPARTMENT_ID)
11 VALUES ( emp_id, ename , job , mgr_id, hiredate ,
12 salary , comm , dept_id );

7 rows merged.

SQL> select * from scott.employees;

EMPNO FIRST_NAME JOB MANAGER_ID HIRE_DATE SALARY COMM DEPARTMENT_ID
------ ---------- --------- ---------- --------- ------ ---- -------------
7369 SMITH CLERK 7902 17-DEC-80 120 0 20
7499 ALLEN SALESMAN 7698 20-FEB-81 300 0 30
7521 WARD SALESMAN 7698 22-FEB-81 540 0 30
7566 JONES MANAGER 7839 02-APR-81 1380 0 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1500 0 30
7698 BLAKE MANAGER 7839 01-MAY-81 1860 0 30
7934 MILLER CLERK 7782 23-JAN-82 4200 0 10

7 rows selected.

SQL> commit;
Commit complete.

RELATED DOCUMENTS
-----------------
Oracle9i Database Administration Guide
Re: oracle9i external table [message #76425 is a reply to message #76364] Tue, 25 June 2002 03:57 Go to previous messageGo to next message
Yasser Abdelrahim
Messages: 2
Registered: June 2002
Junior Member
I am trying to load data using external tables but the data i Have is tab delimited - how do I create the script using tab delimited ?

I tried using the WHITESPACE command but that did not work ...

Can I specify that it should terminate us TAB only I know the hex for tab is X'09' but how do I use it in external table syntax ...
Re: oracle9i external table [message #77140 is a reply to message #76425] Fri, 17 September 2004 11:30 Go to previous messageGo to next message
Paul Goynes
Messages: 1
Registered: September 2004
Junior Member
Did you get the answer on how to specify tab delimiter when creating an external table?
Re: oracle9i external table [message #125193 is a reply to message #77140] Thu, 23 June 2005 14:22 Go to previous messageGo to next message
dbconstructer
Messages: 11
Registered: April 2005
Junior Member
Try this:
Create table
...

ACCESS PARAMETERS
(
records delimited by newline skip 1
FIELDS TERMINATED BY x0'09'
)
...
Re: oracle9i external table [message #320337 is a reply to message #76364] Wed, 14 May 2008 14:00 Go to previous message
whippsa
Messages: 2
Registered: November 2007
Location: USA
Junior Member
Thanks for that. Our problem (and others) is that the data is on another server. That is pretty common yet I haven't found anything on how to use EXTERNAL or UTL_FILE in that situation.
The key seems that we need a way for "CREATE DIRECTORY" to somehow point to a path on another server. Any ideas?

When they split the servers, I had to redo all my sqlldr External stuff.

Those are UNIX boxes, BTW.

[Updated on: Wed, 14 May 2008 14:04]

Report message to a moderator

Previous Topic: Oracle Rules Engine
Next Topic: page refreshing
Goto Forum:
  


Current Time: Fri Mar 29 09:14:48 CDT 2024