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 Go to next message
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 #282737 is a reply to message #282735] Fri, 23 November 2007 04:35 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Check the contents of your log-file (C:\gnis_michigan.log)
Re: create a control file to load data-table [message #282738 is a reply to message #282735] Fri, 23 November 2007 04:36 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

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 #282740 is a reply to message #282735] Fri, 23 November 2007 04:38 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Ohh Yes, You should check the log file, C:\gnis_michigan.log
Re: create a control file to load data-table [message #282749 is a reply to message #282740] Fri, 23 November 2007 05:21 Go to previous messageGo to next message
rebeccah@falcorp.co.za
Messages: 26
Registered: October 2007
Location: midrand
Junior Member

thanks kir_ait

that was simple and straight forward

thanks

from rebeccah
Re: create a control file to load data-table [message #282751 is a reply to message #282735] Fri, 23 November 2007 05:26 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

You welcome,

Did you check C:\gnis_michigan.log file for error?

Regards,
Kiran.
Re: create a control file to load data-table [message #282765 is a reply to message #282751] Fri, 23 November 2007 06:40 Go to previous message
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
Previous Topic: DataPump Error
Next Topic: Sqlldr query
Goto Forum:
  


Current Time: Wed Dec 25 11:33:45 CST 2024