ITPub博客

首页 > 数据库 > Oracle > sql to get data from flat file(zt from asktom)

sql to get data from flat file(zt from asktom)

原创 Oracle 作者:jametong 时间:2005-04-27 08:39:12 0 删除 编辑
Nag -- Thanks for the question regarding "sql to get data from flat file-- this is mind blowing", version 8.1.7
originally submitted on 12-Oct-2001 19:26 Eastern US time, last updated 26-Apr-2005 20:08

You Asked
"In 9i, with the addition of external tables ( the ability to query a FLAT FILE  
with SQL) -- SQLLDR might be "moot". Using the external table and the insert
/*+ append */ I can basically do a direct path load from file to database
without running a command outside of the database."

Tom

We just cant wait, to see how the above is done, what are the new sql clauses we
need to use to achieve the above( there should be some new clauses to do the
above).

Kindly demonstrate the above, this feature will be trend setting and path
breaking.

Nag
and we said...
Ok, here is a demo I use for training internally:

For external tables, we need to use a directory object -- we'll
start with that, mapping to the temp directory

create or replace directory data_dir as 'c:temp'
/

Now, we'll create the external table.
part of its definition is what looks like a control file -- it is


create table external_table
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by ',' )
location ('emp.dat')
)
/


In tempemp.dat I have a file that looks like this:

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
....


(its a dump of the emp table in csv format)


Now you can just:

select * from external_table
/



and now, if you modified the scott/tiger EMP table:

delete from emp where mod(empno,2) = 1
/
update emp set sal = sal/2
/
commit;

You could sync up the flat file with the database table using this single
command:


merge into EMP e1
using EXTERNAL_TABLE e2
on ( e2.empno = e1.empno )
when matched then
update set e1.sal = e2.sal
when not matched then
insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
values ( e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm,
e2.deptno )
/
commit;


that'll update the records in the EMP table from the flat file if they exist OR
it will insert them.

Doing a direct path load would simply be:

insert /*+ append */ into emp select * from external_table;
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94317/viewspace-797005/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    280607