OraFAQ Forum: Server Utilities » inserting negetive number in a number column fails..

Home » RDBMS Server » Server Utilities » inserting negetive number in a number column fails..

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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. ;-)

Report message to a moderator

Previous Topic:	Importing again
Next Topic:	Direct load in append

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 26 13:07:14 CDT 2024