Sqlloader External Table

a Create a directory

Create in the system first

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

again in Sqlplus created in , Let Oracle know this directory

sql> createdirectory dir as '/home/oracle/dir ';

Final authorization

sql> Grant All ondirectory dir-to-public;

two 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,100,17-may-2001,27000,.3,90,ccousins404,john, Richardson,ac_account,205,17-may-2001,5000,0,110,jrichard

View

$ cat Prod_my.data

three Create an external table External

Create tablescott.prod_my (C1 number (5), C2 varchar2 (C3 VARCHAR2), C4 VARCHAR2 (30 ), C5 number (5), C6 date,c7 number (8,3), C8 number (5,2), C9 number (5,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 between columns and columns "," Single Quote Segmentation

optionally enclosed by "" represents a column value is a sentence with ( "" ) Double quotation marks are generated.

MISSING FIELD VALUES arenull representative ( ,, comma and comma can be null

Four Querying 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              121 17-MAY-01       3000           0         50       361  Mark     Jasper             145 17-MAY-01       8000          .1         80        362 Brenda   Starr              200 17-MAY-01       5500           0         10        363 Alex     Alda               145 17-MAY-01        9000        .15         80        401 Jesse    Cromwell           203 17-MAY-01       7000           0         40        402 Abby     Applegate          103 17-MAY-01       9000          .2         60        403 Carol    Cousins            100 17-MAY-01      27000         &nbsP;. 3         90       404  John     richardson        205 17-may-01        5000          0         110

select* from Scott.prod_my; View all information

then import the external table into the database

a Create a table of entities first

CREATE Tablescott.ldr (C1 number (5), C2 Varchar2, C3 varchar2, C4 varchar2 (+), C5 number (5), C6 date,c7 number (8,3) , C8 number (5,2), C9 Number (5,2), C10 varchar2 (30));

two re-create Sqlloader Control Files

$ cd dir$ vi ldr.ctlload datainfile ' prod_my.data ' Appendinto table Scott.ldrfields terminated by ', ' optionally enclosedby ' "' TRAILING nullcols (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10)

Start Import

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

[[Email protected]]$./sqlldr scott/tiger control= '/home/oracle/sqlloader/ldr.ctl ' log= '/home/oracle/sqlloader/ Ldr.log '

The first step ask the location of the file first after investigation, modify infile " /home/oracle/dir/prod_my.data "

Step Two Create a table for the entity again Enter table name after Setup into table " Scott.ldr "

Step three in the infile ' Prod_my.data ' below, add a parameter " Append "

Fourth Step Add a row TRAILING Nullcols ,

Note: Finally, do not add " ; "The semicolon ends, otherwise it will be an error

Explain

Append : Represents if the table has data to continue to add

Trailingnullcols : The delegate can kill the empty line

(due to carriage return, i.e., blank line)

Sqlloader External Table