SQL Loader issue: loading decimal data type
784426Jul 27 2010 — edited Jul 28 2010i'm trying to load the following data to my oracle db using sqlldr.
DATA is
===============================================================
10500158 919602961512 15014122109153571044261 BadSong_MessageSent Sorry,we couldnt understand the song you recorded.Please place ur phone close to the music and try again. To listen songs in ur playlist,call 578787(Re.1/min) 2010-06-26 15:36:22 Rajasthan 9602961512_2010_06_26_15_35_57_IST_727298.wav 10.0227 geetweb 10839 songID is null raj 0.96 40.8225 0 true
===============================================================
It's tab seperated (although it doesn't appear so after posting)
and my control file looks like this .
===============================================================
load data
infile '/home/oracle/sonam/data/SCfilter_backend_dump.test.csv'
TRUNCATE
INTO TABLE SCfilter_backend_dump
FIELDS TERMINATED BY X'09' trailing nullcols(
serialno filler char(20),
userid char(20),
txnid char(45),
songid char(20),
vcode char(20),
title char(100),
album char(100),
status char(45),
messagesent char(4000),
datetime TIMESTAMP 'yyyy-mm-dd hh24:mi:ss',
circle char(20),
filename char(100),
filelength DECIMAL EXTERNAL,
server char(20),
responsetime DECIMAL EXTERNAL,
error char(100),
language char(20),
speed DECIMAL EXTERNAL ,
signalquality DECIMAL EXTERNAL,
blankcount INTEGER (40),
isfilevalid char(20))
===============================================================
while my table looks like this
===============================================================
SQL> desc SCFILTER_BACKEND_DUMP;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERID NOT NULL VARCHAR2(20)
TXNID NOT NULL VARCHAR2(100)
SONGID VARCHAR2(20)
VCODE VARCHAR2(20)
TITLE VARCHAR2(100)
ALBUM VARCHAR2(100)
STATUS VARCHAR2(45)
DATETIME NOT NULL TIMESTAMP(6)
CIRCLE VARCHAR2(45)
FILENAME VARCHAR2(100)
FILELENGTH NUMBER(7,2)
SERVER NOT NULL VARCHAR2(20)
RESPONSETIME NUMBER(7,2)
ERROR VARCHAR2(100)
LANGUAGE VARCHAR2(20)
SPEED NOT NULL NUMBER(7,2)
SIGNALQUALITY NUMBER(7,2)
BLANKCOUNT NUMBER(38)
ISFILEVALID VARCHAR2(20)
MESSAGESENT CLOB
===============================================================
and i'm getting the following error in my log file.
=================================================================
Table SCFILTER_BACKEND_DUMP, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SERIALNO FIRST 20 WHT CHARACTER
(FILLER FIELD)
USERID NEXT 20 WHT CHARACTER
TXNID NEXT 45 WHT CHARACTER
SONGID NEXT 20 WHT CHARACTER
VCODE NEXT 20 WHT CHARACTER
TITLE NEXT 100 WHT CHARACTER
ALBUM NEXT 100 WHT CHARACTER
STATUS NEXT 45 WHT CHARACTER
MESSAGESENT NEXT 4000 WHT CHARACTER
DATETIME NEXT * WHT DATETIME yyyy-mm-dd hh24:mi:ss
CIRCLE NEXT 20 WHT CHARACTER
FILENAME NEXT 100 WHT CHARACTER
FILELENGTH NEXT * WHT CHARACTER
SERVER NEXT 20 WHT CHARACTER
RESPONSETIME NEXT * WHT CHARACTER
ERROR NEXT 100 WHT CHARACTER
LANGUAGE NEXT 20 WHT CHARACTER
SPEED NEXT * WHT CHARACTER
SIGNALQUALITY NEXT * WHT CHARACTER
BLANKCOUNT NEXT 40 INTEGER
ISFILEVALID NEXT 20 WHT CHARACTER
value used for ROWS parameter changed from 64 to 42
Record 1: Rejected - Error on table SCFILTER_BACKEND_DUMP.
ORA-01460: unimplemented or unreasonable conversion requested
Table SCFILTER_BACKEND_DUMP:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 251580 bytes(42 rows)
===============================================================
i'm not able to get why this method is failing for loading decimal data, while its mentioned in all the manuals.
What's wrong with the control file description. In fact INTEGER EXTERNAL works fine for loading if data is integer only.
Thanks and regards,
Sonam