APEX Development

A non APEX post but still Oracle. I thought it might be useful for me to post how we loaded the Geonames data into Oracle via sqlldr as I ran into a little bit of trouble initially.

Note: we downloaded the GB (i.e. UK) Geoname data for the below example.

create table GEONAME
(
GEONAME_ID NUMBER,
NAME VARCHAR2(200),
ASCIINAME VARCHAR2(200),
ALTERNATENAMES NCLOB,
LATITUDE NUMBER(19,16),
LONGITUDE NUMBER(19,16),
FEATURE_CLASS VARCHAR2(1),
FEATURE_CODE VARCHAR2(10),
COUNTRY_CODE VARCHAR2(2),
CC2 VARCHAR2(60),
ADMIN1_CODE VARCHAR2(20),
ADMIN2_CODE VARCHAR2(80),
ADMIN3_CODE VARCHAR2(20),
ADMIN4_CODE VARCHAR2(20),
POPULATION NUMBER,
ELEVATION NUMBER,
GTOPO30 NUMBER,
TIMEZONE_ID VARCHAR2(50),
MODIFICATION_DATE DATE
)

OPTIONS (ROWS=8192, BINDSIZE=5000000, READSIZE=5000000, ERRORS=100)
LOAD DATA CHARACTERSET UTF8 BYTEORDERMARK CHECK
INFILE './GB.txt'
BADFILE './GB.BAD'
DISCARDFILE './GB.DSC' DISCARDMAX 50
INTO TABLE geoname FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
(
GEONAME_ID,
NAME,
ASCIINAME,
ALTERNATENAMES char(8000),
LATITUDE,
LONGITUDE,
FEATURE_CLASS,
FEATURE_CODE,
COUNTRY_CODE,
CC2,
ADMIN1_CODE,
ADMIN2_CODE,
ADMIN3_CODE,
ADMIN4_CODE,
POPULATION,
ELEVATION,
GTOPO30,
TIMEZONE_ID,
MODIFICATION_DATE DATE "YYYY-MM-DD"
)