Home » RDBMS Server » Server Utilities » create a control file to load data-table (oracle 10g, win 2000)
| create a control file to load data-table [message #282735] |
Fri, 23 November 2007 04:18  |
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
|
Junior Member |
|
|
hi guys
i'm trying to load data using SQLLDR
i've created a control file
LOAD DATA
APPEND INTO TABLE gfn_gnis_feature_names
(
gfn_state_abbr CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_feature_name CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_feature_type CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_county_name CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_fips_state_code FILLER INTEGER EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_fips_county_code FILLER INTEGER EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_primary_latitude_dms CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_primary_longitude_dms CHAR TERMINATED BY "," ENCLOSED BY '"',
gfn_primary_latitude_dec FILLER DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_primary_longitude_dec FILLER DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_source_latitude_dms FILLER CHAR
TERMINATED BY "," ENCLOSED BY '"',
gfn_source_longitude_dms FILLER CHAR
TERMINATED BY "," ENCLOSED BY '"',
gfn_source_latitude_dec FILLER DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_source_longitude_dec FILLER DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_elevation DECIMAL EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_population INTEGER EXTERNAL
TERMINATED BY "," ENCLOSED BY '"',
gfn_cell_name CHAR TERMINATED BY "," ENCLOSED BY '"'
)
table
CREATE TABLE gfn_gnis_feature_names (
gfn_state_abbr CHAR(2),
gfn_feature_name VARCHAR2(60),
gfn_feature_type VARCHAR2(9),
gfn_county_name VARCHAR2(35),
gfn_primary_latitude_dms CHAR(7),
gfn_primary_longitude_dms CHAR(8),
gfn_elevation NUMBER(7,2),
gfn_population NUMBER(10),
gfn_cell_name VARCHAR2(30)
) TABLESPACE users;
now when i run the following line in prompt
sqlldr scott/tiger control=C:\gnis.ctl log=C:\gnis_michigan.log data=C:\mi_deci.dat
i get the folowing output
C:\oracle\product\10.2.0\db_1\BIN>sqlldr scott/tiger control=C:\gnis.ctl log=C:\
gnis_michigan.log data=C:\mi_deci.dat
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 23 11:51:12 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
C:\oracle\product\10.2.0\db_1\BIN>sqlldr scott/tiger control=C:\gnis.ctl log=C:\
gnis_michigan.log data=C:\mi_deci.dat
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 23 11:55:58 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
C:\oracle\product\10.2.0\db_1\BIN>
1 it does not show/display if the script has been loaded succefull
2 when i select from the table there is no data
3 is there anything i've missed?
3
somebody have a simple example of a control file i be gld to try it out
as i have a load of data to populate as a testing_data in the system
kind regards,
rebecah
[Updated on: Fri, 23 November 2007 04:20] Report message to a moderator |
|
|
|
|
|
| Re: create a control file to load data-table [message #282738 is a reply to message #282735] |
Fri, 23 November 2007 04:36  |
|
|
load data
into table CUSTOMERS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
PHONE,
NAME,
LAST,
COMPANY,
ALTERNATE,
EMAIL,
GENDER,
DOBDAY,
DOBMONTH,
DOBYEAR,
MARITAL,
ADDRESS,
STATE,
CITY,
TERMINAL,
DATA,
DOPDAY,
DOPMONTH,
DOPYEAR,
ACTDATE1,
ACTDATE2,
ACTDATE3,
TERMDATE1,
TERMDATE2,
TERMDATE3,
RELSTATE,
RELTYPE,
AGENT,
LOCATION,
CURDATE,
KEYACCOUNT
)
|
|
|
|
|
|
|
|
|
|
| Re: create a control file to load data-table [message #282765 is a reply to message #282751] |
Fri, 23 November 2007 06:40 |
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
|
Junior Member |
|
|
yes i did, but then it said something line
error occured because of where clause
but then the whole controle file was confusing
here is the simple one
SQL> create table mine(
2 PHONE VARCHAR2(15),
3 NAME VARCHAR2(15),
4 LAST_NaME VARCHAR2(20),
5 DATES DATE);
Table created.
SQL> select * from mine;
no rows selected
my results
SQL> select * from mine;
PHONE NAME LAST_NAME DATES
--------------- --------------- -------------------- ---------
0835426785 'sd' dd 12-APR-07
0835426785 sd dd 12-APR-07
0835426785 sd dd 12-APR-07
0835426785 sd dd 12-APR-07
0835426785 sd dd 12-APR-07
create log file, (it is not compulsory to specify the fields types in a control file unless a fields is a date)
load data
INFILE'c:\rebbs1.dat'
append into table mine
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
PHONE,
NAME,
LAST_name,
DATES DATE'DD/MM/YYYY'
)
i my cmd prompt i run the following statement(s) in my oracle home directory the rerults below:
C:\oracle\product\10.2.0\db_1\BIN>sqlldr rebbs/rebbs control=C:\rebbs.ctl log=C:
\rebbs.log
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Nov 23 14:23:17 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
Commit point reached - logical record count 4
C:\oracle\product\10.2.0\db_1\BIN>
my results from table "mine"
SQL> select * from mine
SQL> /
PHONE NAME LAST_NAME DATES
--------------- --------------- -------------------- ---------
0835426785 'sd' dd 12-APR-07
0835426785 sd dd 12-APR-07
0835426785 sd dd 12-APR-07
0835426785 sd dd 12-APR-07
0835426785 sd dd 12-APR-07
thanks for all you helps
this as a a lot more easier that what i posted earlier
from rebeccah
|
|
|
|
Goto Forum:
Current Time: Wed Apr 17 19:18:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
