外部表

Drop Table  tb_sql_ldr_fei;
CREATE TABLE tb_sql_ldr_fei(
SERVICEIDITEM   number(12),
AREAIDITEM     Number(12),
GROUPIDITEM     Number(12),
      CONTEXTID    varchar2(50),
      ORDERID    varchar2(50),
ITEMID     Number(12),
ITEMNUM   Number(12),
ITEMAMOUNT    Number(12),
UPDATETIME     Date,
APPCODE     Number(12),
ACCOUNTID   Number(12)
                     )
 ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY dir_dbdp
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
SKIP 1
FIELDS TERMINATED By x'09'
(SERVICEIDITEM INTEGER EXTERNAL (12),
AREAIDITEM Integer EXTERNAL (12),
GROUPIDITEM Integer EXTERNAL (12),
 CONTEXTID    Char(50),
ORDERID Char(50),
ITEMID   INTEGER EXTERNAL (12),
ITEMNUM  INTEGER EXTERNAL (12),
ITEMAMOUNT   INTEGER EXTERNAL (12),
UPDATETIME Date'yyyy-mm-dd:hh24:mi:ss',
APPCODE    INTEGER EXTERNAL (12),
ACCOUNTID   INTEGER EXTERNAL (12)
))
LOCATION (xxx.txt'));

 

 

 

====

 

 

 

Drop Table  tb_sql_ldr_fei;
CREATE TABLE tb_sql_ldr_fei(
remark1   Varchar2(100),
remark2  Varchar2(100),
remark3  Varchar2(100),
remark4  Varchar2(100)
                     )
 ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY dir_dbdp
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED By x'09'
(
remark1 Char(100),
remark2 Char(100),
remark3 Char(100),
remark4 Char(100)
))
LOCATION ('ptid-2011-03-22.txt'))
reject limit Unlimited;

Create Table tmp_1 As Select * From

 

 

----

 

 

 

Drop Table  tb_sql_ldr_fei;
CREATE TABLE tb_sql_ldr_fei(
remark1   Varchar2(400)
                     )
 ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY dir_dbdp
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
nobadfile
nologfile
nodiscardfile
)
LOCATION ('ptid-2011-03-22.txt'))
reject limit unlimited
;