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
;