Oracle Database Discussions

Hi guys,

I am having a unknown issue while loading .dbf file by sql loader.
I need to load .dbf data into oracle table.for this I converted .dbf file by just changing its extension as .csv .
--------------------------------------------------------------------------
file structure after changing .dbf to .csv --

C_N_NUMBER,COMP_CODE,CPT_CODE,C_N_AMT,CM_NUMBER
1810/4,LKM,30,45,683196
1810/5,LKM,30,45,683197
1810/6,LKM,30,45,683198
1810/7,LKM,30,135,683200
1810/8,LKM,30,90,683201
1810/9,LKM,1,45,683246
1810/9,LKM,2,90,683246
1810/10,LKF,1,90,683286
2810/13,LKJ,1,50.5,680313
2810/14,LKJ,1,50,680316
1910/1,LKQ,1,90,680344
3910/2,LKF,1,238.12,680368
3910/3,LKF,1,45,680382
3910/4,LKF,1,45,680395
7910/5,LKS,1,45,680397
7910/6,LKS,1,90,680400
7910/7,LKS,1,45,680401
7910/8,LKS,1,238.12,680414
7910/9,LKS,1,193.12,680415
7910/10,LKS,1,45,680490
------------------------------------------------------------------------------------
then I am loading it by sql loader.but I am getting always error below ...
------------------------------------------------------------------------------------------------------------

Record 1: Rejected - Error on table C_N_DETL_TAB, column CPT_CODE.
ORA-01438: value larger than specified precision allowed for this column

Record 2: Rejected - Error on table C_N_DETL_TAB, column CPT_CODE.
ORA-01438: value larger than specified precision allowed for this column

------------------------------------------------------------------------------------------------------------

table structure-
create table C_N_DETL_tab
(
"C_N_NUMBER" VARCHAR2(13),
"COMP_CODE" VARCHAR2(3),
"CPT_CODE" NUMBER(4),
"C_N_AMT" NUMBER(20,18),
"CM_NUMBER" NUMBER(7)
)
------------------------------------------------------------------------------------------------------------
control file-

options(skip=1)
load data
infile '/softdump/pc/C_N_DETL.csv'
badfile '/softdump/pc/C_N_DETL.bad'
discardfile '/softdump/pc/C_N_DETL.dsc'
into table C_N_DETL_tab
truncate
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
C_N_NUMBER CHAR,
COMP_CODE CHAR,
CPT_CODE INTEGER,
C_N_AMT INTEGER,
CM_NUMBER INTEGER
)
------------------------------------------------------------------------------------------------------------

but guys when I am increasing size of all columns of tabel upto its max value then data is loaded but when I am checking column max length after data loaded then its very less..

changed table structure-
create table C_N_DETL_tab
(
"C_N_NUMBER" VARCHAR2(130),
"COMP_CODE" VARCHAR2(30),
"CPT_CODE" NUMBER(32), ---- max value of number
"C_N_AMT" NUMBER(32,18), ---- max value of number
"CM_NUMBER" NUMBER(32) ---- max value of number
)

---------------------------------------------------------------------------------------------------------
now i ma running ...
sqlldr express/express control=C_N_DETL.ctl log=C_N_DETL.log

o/p-

Table C_N_DETL_TAB, 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
------------------------------ ---------- ----- ---- ---- ---------------------
C_N_NUMBER FIRST * , O(") CHARACTER
COMP_CODE NEXT * , O(") CHARACTER
CPT_CODE NEXT 4 INTEGER
C_N_AMT NEXT 4 INTEGER
CM_NUMBER NEXT 4 INTEGER


Table C_N_DETL_TAB:
20 Rows successfully loaded.
0 Rows 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.

---------------------------------------------------------------------------------------------------------

select max(length( CPT_CODE))from C_N_DETL_tab ---> 9


can u tell me why I need to increase size of table columns upto max value?although length of data is soo much less.

kindly check it..thnx in advance...


rgds,
pc

Edited by: PC on Oct 13, 2010 9:46 PM