в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:55:01Z

1 версия

Новая страница

==FIELDS TERMINATED BY WHITESPACE==



<source lang="sql">

create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
-- FIELDS TERMINATED BY x"09"
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales BC
drop table dept;
</source>

==Indicate columns in load data statement==



<source lang="sql">

create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """
( DEPTNO,
FILLER_1 FILLER,
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,"BC,USA"

drop table dept;
</source>

==Leave start position open in load data statement==



<source lang="sql">

create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(*:16),
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting BC,USA

drop table dept;
</source>

==Load CSV data to a table==



<source lang="sql">

set echo on
create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ","
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10,Sales,BC
20,Accounting,BC
30,Consulting,BC
40,Finance,BC
drop table dept;
</source>

==Load data and set field terminated sign and OPTIONALLY ENCLOSed sign==



<source lang="sql">

create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10,Sales,"""USA"""
20,Accounting,"BC,USA"
30,Consulting,BC
40,Finance,BC
50,"Finance","",BC
60,"Finance",,BC
drop table dept;
</source>

==Set char(count) for load data statement==



<source lang="sql">

create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1) char(2),
DNAME position(*) char(14),
LOC position(*) char(13),
ENTIRE_LINE position(1) char(29)
)
BEGINDATA
10Accounting BC,USA

drop table dept;
</source>

==Set date format in load data statement==



<source lang="sql">

create table dept
( deptno number(2) constraint emp_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
(DEPTNO,
DNAME,
LOC,
LAST_UPDATED date "dd/mm/yyyy"
)
BEGINDATA
10,Sales,BC,1/5/2000
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000
40,Finance,BC,15/3/2001
drop table dept;
</source>

==Set ENTIRE_LINE in load data statement==



<source lang="sql">

create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(3:16),
LOC position(17:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting BC,USA

drop table dept;
</source>

==Set entry line in load data statement==



<source lang="sql">

create table dept
( deptno number(2) constraint emp_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date "dd/mm/yyyy",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,BC,1/5/2000
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000
40,Finance,BC,15/3/2001

drop table dept;
</source>

==Set value position in load statement==



<source lang="sql">

create table dept
( deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(3:16),
LOC position(17:29)
)
BEGINDATA
10Accounting BC,USA
drop table dept;
</source>

==Trailing null columns when loading data==



<source lang="sql">

create table dept
( deptno number(2) constraint emp_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
/

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date "dd/mm/yyyy",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,BC,1/5/2000
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000
40,Finance,BC,15/3/2001
drop table dept;
</source>

==use case statement to check the value in load data statement==



<source lang="sql">

create table dept
( deptno number(2) constraint emp_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "case when length(:last_updated) <= 10
then to_date(:last_updated,"dd/mm/yyyy")
else to_date(:last_updated,"dd/mm/yyyy hh24:mi:ss")
end"
)
BEGINDATA
10,Sales,BC,1/5/2000 12:03:03
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000 01:23:00
40,Finance,BC,15/3/2001
drop table dept;
</source>

==Use string function in load data statement==



<source lang="sql">

create table dept
( deptno number(2) constraint emp_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date "dd/mm/yyyy"
)
BEGINDATA
10,Sales,BC,1/5/2000
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000
40,Finance,BC,15/3/2001
drop table dept;
</source>

==Use user-defined columns in load data statement==



<source lang="sql">

create table dept
( deptno number(2) constraint emp_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
/
create or replace
function my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( "dd-mon-yyyy", "dd-month-yyyy",
"dd/mm/yyyy",
"dd/mm/yyyy hh24:mi:ss" );
l_return date;
begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;
if ( l_return is null )
then
l_return :=
new_time( to_date("01011970","ddmmyyyy") + 1/24/60/60 *
p_string, "GMT", "EST" );
end if;
return l_return;
end;
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )"
)
BEGINDATA
10,Sales,BC,01-april-2001
20,Accounting,BC,13/04/2001
30,Consulting,BC,14/04/2001 12:02:02
40,Finance,BC,987268297
50,Finance,BC,02-apr-2001
60,Finance,BC,Not a date
drop table dept;
</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Table/CSV Load - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия