I taught a session on SQLloader over the weekend, so I am going to post the highlights and the exercises I created for the students on this space.
What is Sqlloader?
It is a database tool for loading large volumes of data into the database. It is faster than using an insert and more reliable too. Also, it is free because it comes with the Oracle database utilities.
How to invoke Sqlloader
Lauch sqlloader using the command below:
sqlldr username/password control=controlfile
e.g
sqlldr system/**** control=my_control_file.ctl
Valid Keywords/arguments
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header, errors, discards, partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
example 1: Use sqlloader to load data into table PERSON
log in as SYSTEM
SQL> create table PERSON
(SSN number(15),
National_ID number(15),
Last_name varchar2(30),
first_name varchar2(15),
sex char(1)
);
create a control file- "person_ctl.txt"
add this.....
LOAD DATA
INFILE 'c:\person_data.txt'
BADFILE 'c:\person_data_bad.txt'
APPEND
INTO TABLE PERSON
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
( SSN,
National_ID,
Last_name,
first_name,
sex
)
create an infile- "person_data.txt"
add this.....
77770101,7777801,JEFFEREY,KIDMAN,m
77770102,7777802,Clinton R,miller,m
77770103,7777803,ALLEN D,IVES,m
77770104,7777804,Matthew,MAXWELL,f
77770105,7777805,ANTHONY J,REISNER,m
77770106,7777806,CLINT A,KOCH,m
77770107,7777807,ORLANDO,Desantis,m
77770108,7777808,JUAN C,ZIMMERMAN,m
77770109,7777809,STEVE J,EVANS,m
77770110,7777810,Robert D,DAVIS,m
77770111,7777811,Michael,McClendon,m
77770112,7777812,JONATHAN,CONGER,m
77770113,7777813,DAVID F,LOVITT,m
77770114,7777814,Ernesto,FLESHMAN,m
77770115,7777815,Joshua B,pierce,m
77770116,7777816,Justin,OLIVER,m
77770117,7777817,TODD J,Jacobs,m
77770118,7777818,Conner,BROWN,m
77770119,7777819,Dewey,WOOD,m
77770120,7777820,FRANK C,ARAUJO,m
77770121,7777821,SCOTT A,OBYRNE,m
77770122,7777822,JESSE W,HARDIN,m
77770123,7777823,MATTHEW D,CASTOR,m
77770124,7777824,JOSHUA,ANDREWS,m
77770125,7777825,JUSTIN B,Depew,m
77770126,7777826,MATTHEW F,FORST,m
77770127,7777827,ALFONSO L,OGBORN,m
77770128,7777828,COREY J,DINOFF,m
77770129,7777829,CHRISTOPHER J,COMETA,m
77770130,7777830,PEDRO L,WEBB,m
77770131,7777831,ZACHERY R,GUTIERREZ,m
77770132,7777832,George,SAIKU,m
77770133,7777833,WILLIAM P,FISHER,m
77770134,7777834,ANTHONY R,STOWBUNKO,m
77770135,7777835,Terry,GILCHRIST,m
77770136,7777836,ERIC M,LEE,m
77770137,7777837,CHARLES W,MONTGOMERY,m
77770138,7777838,CHRISTOPHER S,SZOKE,m
77770139,7777839,RANDALL J,Konig,m
77770140,7777840,Robert E,TURNBOW,m
77770141,7777841,JUSTIN R,GUTIERREZ,m
77770142,7777842,NICHOLAS P,klein,m
77770143,7777843,ROBERT W,ALLEY,m
77770144,7777844,PAUL D,SANTOCONO,m
77770145,7777845,KORY J,GOMEZ,m
77770146,7777846,WALTER T,STEADMAN,m
77770147,7777847,JOSHUA D,Martin,m
77770148,7777848,CHAN P,GWIZDAK,m
77770149,7777849,CHRISTOPHER W,OKKEN,m
77770150,7777850,NAQUAN D,CARMICHAEL,m
Launch sqlloader from c:\
c:\> sqlldr system/**** control=person_ctl.txt
example 2: Use sqlloader to load data into table SCHOOL
log in as SYSTEM
SQL> create table SCHOOL
(SCH_ID number(15),
SSN number(15),
state_id number(7),
county_id number(7),
county number(7),
state char(2)
);
create a control file- "sch_ctl.txt"
add this.....
LOAD DATA
INFILE 'c:\sch_data.txt'
BADFILE 'c:\sch_data_bad.txt'
APPEND
INTO TABLE PERSON
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
( SCH_ID,
SSN,
state_id,
county_id,
county,
state
)
create an infile- "sch_data.txt"
add this.....
7777801,77770101,262,2020,2020,ga
7777802,77770102,262,2020,2020,ma
7777803,77770103,262,2020,2020,la
7777804,77770104,262,2020,2020,tx
7777805,77770105,262,2020,2020,md
7777806,77770106,262,2020,2020,mi
7777807,77770107,262,2020,2020,mo
7777808,77770108,262,2020,2020,mo
7777809,77770109,262,2020,2020,mi
7777810,77770110,262,2020,2020,tx
7777811,77770111,262,2020,2020,tx
7777812,77770112,262,2020,2020,tx
7777813,77770113,262,2020,2020,md
7777814,77770114,262,2020,2020,md
7777815,77770115,262,2020,2020,sd
7777816,77770116,262,2020,2020,id
7777817,77770117,262,2020,2020,mi
7777818,77770118,262,2020,2020,dc
7777819,77770119,262,2020,2020,dc
7777820,77770120,262,2020,2020,dc
7777821,77770121,262,2020,2020,dc
7777822,77770122,262,2020,2020,dc
7777823,77770123,262,2020,2020,dc
7777824,77770124,262,2020,2020,mi
7777825,77770125,262,2020,2020,dc
7777826,77770126,262,2020,2020,md
7777827,77770127,262,2020,2020,ms
7777828,77770128,262,2020,2020,tn
7777829,77770129,262,2020,2020,tx
7777830,77770130,262,2020,2020,oh
7777831,77770131,262,2020,2020,mi
7777832,77770132,262,2020,2020,az
7777833,77770133,262,2020,2020,ca
7777834,77770134,262,2020,2020,oh
7777835,77770135,262,2020,2020,ga
7777836,77770136,262,2020,2020,ny
7777837,77770137,262,2020,2020,ny
7777838,77770138,262,2020,2020,nj
7777839,77770139,262,2020,2020,nj
7777840,77770140,262,2020,2020,pa
Launch sqlloader from c:\
c:\> sqlldr system/**** control=sch_ctl.txt
NOTE:
To avoid errors, launch SQLldr from the path where your control file is located,
e.g if your control file is c:\database\control1.ctl, then run sqlldr from 'c:\database'
No comments:
Post a Comment