Sunday, May 24, 2009

การ Select ข้อมูลจากเท็กซ์ไฟล์

ข้อเขียนนี้ช่วยฉัน: 
Date: 29/03/2009

เราสามารถ Select ข้อมูลจากเท็กซ์ไฟล์ข้างนอกระบบฐานข้อมูลได้

1. สมมติเรามีเท็กซ์ไฟล์ที่มีข้อมูลดังนี้
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20

2. การ Select จากไฟล์ จะต้องเก็บไฟล์ไว้ในไดเรคทอรีที่ Oracle รู้จัก โดยในตัวอย่างนี้เราจะเก็บไฟล์ไว้ในไดเรคทอรีบนดาต้าเบสที่ชื่อว่า 'DATA_PUMP_DIR' ซึ่งมีมากับดาต้าเบสโดยดีฟอลต์ การจะเช็คดูว่ามี directory แล้วหรือยังและไดเรคทอรีดังกล่าวแม็ปกับไดเรคทอรีบน OS ใด เราสามารถใช้คำสั่ง (จะต้อง Login user ที่มีสิทธิ์ DBA)
SQL> select * from dba_directories where directory_name = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------ --------------------- ----------------------------------
SYS DATA_PUMP_DIR C:\oraclexe\app\oracle\admin\XE\dpdump\
เราอาจจะสร้าง directory ใหม่เพื่อให้แม็ปกับ directory ที่เราต้องการก็ได้โดยการสร้างจะต้องมีสิทธิ์ที่เป็น DBA เช่นกัน

3. Save ไฟล์ในข้อ 1 ด้วยชื่อ "emp.dat" ไว้ในไดเรคทอรีที่แม็ปไว้กับไดเรคทอรี DATA_PUMP_DIR ใน Oracle
4. ผู้จะสร้าง External Table จะต้องมีสิทธิ์ Read,write ในไดเรคทอรีที่เก็บไฟล์ (จะต้องให้ user ที่มีสิทธิ์ DBA grant ให้)
SQL> grant read,write on directory data_pump_dir to scott;
Grant succeeded

5. จากนั้นล็อกอินด้วย user ทีต้องการสร้างตารางแล้วสร้างตารางดังข้างล่าง
SQL> create table emp_ext
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2)
)
organization external
(type oracle_loader
default directory data_pump_dir
access parameters
(
records delimited by newline
fields terminated by ','
)
location ('emp.dat')
)
reject limit 1000;
โดยคอลัมน์ที่กำหนดจะต้องสอดคล้องกับข้อมูลในไฟล์
- ตรง 'location' เป็นตัวกำหนดว่าชื่อไฟล์ที่จะให้ไปอ่านชื่ออะไร
- โดย Oracle จะไปอ่านไฟล์ดังกล่าวตามที่กำหนดไว้ใน default directory
- 'fields terminated by' เป็นตัวกำหนดว่าข้อมูลแต่ละคอลัมน์ถูกแยกจากกันด้วยสัญญลักษณ์อะไร
- 'reject limit' เป็นตัวกำหนดว่าถ้ามีข้อผิดพลาดในการอ่านไม่เกินจำนวนเท่าไรจึงจะแสดง error และไม่ต้องอ่านต่อ

6. หลังจากสร้างตารางเสร็จแล้ว เราสามารถ select ข้อมูลได้เหมือนตารางปกติ
SQL> select * from emp_ext;

No comments:

Post a Comment