Shulou(Shulou.com)06/01 Report--

Create a directory

Create it under the system first

$cd / home/oracle$ mkdir dir$ cd dir$ pwd

Then create it in sqlplus and let oracle know about this directory

SQL > createdirectory dir as'/ home/oracle/dir'

Final authorization

SQL > grant all ondirectory dir to public

Create a data source file

$cd dir

$vi prod_my.data360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega403,Carol,Cousins,AD_VP 100recover17 Muhammayuki 2001 pr 27000pr. 3pr 90pr ccousins404pr Johnrecoveri RichardsonrecoveryACCOUNT 205pr 17MAYPY 2001 pr 05000pr 0jrichard 110jhard

View

$cat prod_my.data

3. Create an external table external

CREATE TABLEscott.prod_my (C1 number (5), c2 varchar2 (20), c3 varchar2 (20), c4 varchar2 (30), c5 number (5), c6 date,c7 number (8 date,c7 number 3), c8 number (5 number 2), c9 number (5 number 2), c10 varchar2 (30) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADERDEFAULT DIRECTORY dir ACCESS PARAMETERS (FIELDS TERMINATED BY ") "OPTIONALLY ENCLOSED BY'" 'MISSING FIELD VALUES ARE NULL) LOCATION (' prod_my.data'))

explain

"FIELDS TERMINATED BY", "represents column to column separation", "single quotation mark division"

OPTIONALLY ENCLOSED BY "" represents a column value that is enclosed in ("") double quotation marks.

MISSING FIELD VALUES ARENULL represents a null value between a comma and a comma

Four query external tables

Col c2 for a8col c3 for a10col c4 for a10col c10 for a10selectc1,c2,c3,c5,c6,c7,c8,c9 from scott.prod_my C1 C2 C3 C5 C6 C7 C8 C9- -360 Jane Janus 12117-MAY-01 3000 050361 Mark Jasper 14517-MAY-01 8000 .180362Brenda Starr 20017-MAY-01 5500 010363 Alex Alda 14517-MAY -01 9000. 15 80 401 Jesse Cromwell 203 17-MAY-01 7000 0 40 402 Abby Applegate 9000 17-MAY-01 9000. 2 60 403 Carol Cousins 100 17-MAY-01 27000. 3 90 404 John Richardson 205 17-MAY-01 5000 0 110

Select* from scott.prod_my; to view all information

Then import the external table into the database

First, create the table of the entity

CREATE TABLEscott.ldr (C1 number (5), c2 varchar2 (20), c3 varchar2 (20), c4 varchar2 (30), c5 number (5), c6 date,c7 number (8 number 3), c8 number (5 number 2), c9 number (5 varchar2 2), c10 varchar2 (30))

Second, create a sqlloader control file

$cd dir$ vi ldr.ctlload datainfile'prod_my.data'appendinto table scott.ldrfields terminated by','optionally enclosedby''TRAILING NULLCOLS (c1meme, c2, c3, c4, 5, 6, c7, c8, c9, and c10)

Start importing

/ u01/app/oracle/product/11gr2/db_1/bin

[oracle@HE3bin] $. / sqlldr scott/tiger control='/home/oracle/sqlloader/ldr.ctl'log='/home/oracle/sqlloader/ldr.log'

The first step is to ask the location of the file, and then modify the file [/ home/oracle/dir/prod_my.data]

The second step is to create the entity table and enter the table name into table [scott.ldr] after the table is established.

Step 3. Under infile 'prod_my.data', add a parameter [append].

Step 4: add a row of TRAILING NULLCOLS

Note: do not end with the semicolon [;] at last, otherwise an error will be reported.

explain

Append: if there is data in the table, you can continue to add it.

TRAILINGNULLCOLS: means you can kill the blank lines.

(due to carriage return, that is, blank lines)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.