Home » RDBMS Server » Server Utilities » inserting negetive number in a number column fails..
inserting negetive number in a number column fails.. [message #72919] |
Thu, 11 December 2003 12:14 |
Sabrina
Messages: 76 Registered: February 2002
|
Member |
|
|
My table structure looks like,
CREATE TABLE ZIP_CODES_REF
(ZIPCODE NUMBER(5) NOT NULL,
ZIPCODE_TYPE CHAR(1),
CITY VARCHAR2(30),
CITY_TYPE CHAR(1),
STATE VARCHAR2(75),
STATE_CODE CHAR(2),
AREA_CODE NUMBER(3),
LATITUDE NUMBER(9,6),
LONGITUDE NUMBER(12,6))
TABLESPACE APP_TABLES
My Data looks like premium.txt(I am pasting only 2 record):
00501,U,Holtsville,D,New York,NY,631,40.8144,-73.0472
00544,U,Holtsville,D,New York,NY,631,40.8141,-73.0480
My .ctl file looks like,
LOAD DATA
INFILE 'premium.txt'
APPEND
INTO TABLE ZIP_CODES_REF
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ZIPCODE,
ZIPCODE_TYPE,
CITY,
CITY_TYPE,
STATE,
STATE_CODE,
AREA_CODE,
LATITUDE,
LONGITUDE
)
When i load my file using the shell script, it gives me the following error on the LONGITUDE column:
Table ZIP_CODES_REF, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ZIPCODE FIRST * , CHARACTER
ZIPCODE_TYPE NEXT * , CHARACTER
CITY NEXT * , CHARACTER
CITY_TYPE NEXT * , CHARACTER
STATE NEXT * , CHARACTER
STATE_CODE NEXT * , CHARACTER
AREA_CODE NEXT * , CHARACTER
LATITUDE NEXT * , CHARACTER
LONGITUDE NEXT * , CHARACTER
Record 1: Rejected - Error on table ZIP_CODES_REF, column LONGITUDE.
ORA-01722: invalid number
Record 2: Rejected - Error on table ZIP_CODES_REF, column LONGITUDE.
ORA-01722: invalid number
WHY IS IT GIVING ME, "INVALID NUMBER" ERROR??
Thank you very much in advance.
|
|
|
Re: inserting negetive number in a number column fails.. [message #72920 is a reply to message #72919] |
Fri, 12 December 2003 08:32 |
Thomas G.
Messages: 10 Registered: November 2003
|
Junior Member |
|
|
Hi,
Try specifiying the datatype in the controll file.
It should be something like :
LOAD DATA
INFILE 'premium.txt'
APPEND
INTO TABLE ZIP_CODES_REF
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ZIPCODE INTERGER EXTERNAL,
ZIPCODE_TYPE CHAR,
CITY CHAR,
CITY_TYPE CHAR,
STATE CHAR,
STATE_CODE CHAR,
AREA_CODE INTEGER EXTERNAL,
LATITUDE DECIMAL EXTERNAL,
LONGITUDE DECIMAL EXTERNAL
)
Good Luck. ;-)
|
|
|
Goto Forum:
Current Time: Tue Dec 24 19:54:07 CST 2024
|