| SQL*Loader - partial load where nulls not allowed problem [message #173178] |
Fri, 19 May 2006 23:18  |
gbell
Messages: 3
Registered: May 2006
|
Junior Member |
|
|
Hi,
I'm trying to append into a table of 5 columns but only have 3 columns to load from the input file.
If I change the control file to remove the TRAILING NULLCOLS
I get this error
Record 1: Rejected - Error on table MSF010_TF, column XDATA.
Column not found before end of logical record (use TRAILING NULLCOLS)
Many thanks
Greg
Table
TABLE_TYPE NOT NULL CHAR(4)
TABLE_CODE NOT NULL CHAR(18)
TABLE_DESC NOT NULL CHAR(50)
ASSOC_REC NOT NULL CHAR(50)
XDATA NOT NULL CHAR(1)
Control File
LOAD DATA
INFILE 'msf010_tf.dat'
APPEND INTO TABLE MSF010_TF
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
TABLE_TYPE
, TABLE_CODE
, TABLE_DESC
, ASSOC_REC FILLER
, XDATA FILLER
)
Sample input data
TF|0101.10.00.0|-Pure-bred breeding animals||
TF|0101.90.10.0|--Race horses||
TF|0101.90.20.0|--Other horses||
TF|0101.90.90.0|--Other||
TF|0102.10.00.0|-Pure-bred breeding animals||
TF|0102.90.10.0|--Oxen||
TF|0102.90.20.0|--Buffaloes||
TF|0102.90.90.0|--Other||
TF|0103.10.00.0|-Pure-bred breeding animals||
TF|0103.91.00.0|--Weighing less than 50 kg||
Errors from logfile
Table MSF010_TF, 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
------------------------------ ---------- ----- ---- ---- ---------------------
TABLE_TYPE FIRST * | CHARACTER
TABLE_CODE NEXT * | CHARACTER
TABLE_DESC NEXT * | CHARACTER
ASSOC_REC NEXT * | CHARACTER
(FILLER FIELD)
XDATA NEXT * | CHARACTER
(FILLER FIELD)
Record 1: Rejected - Error on table MSF010_TF.
ORA-01400: cannot insert NULL into ("ELLIPSE"."MSF010_TF"."ASSOC_REC")
Record 2: Rejected - Error on table MSF010_TF.
ORA-01400: cannot insert NULL into ("ELLIPSE"."MSF010_TF"."ASSOC_REC")
|
|
|
|
|
|
|
|
| Re: SQL*Loader - partial load where nulls not allowed problem [message #173206 is a reply to message #173182] |
Sat, 20 May 2006 04:42  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
How about inserting some constant dummy value if null is encountered?
oracle@mutation#cat somectl.ctl
LOAD DATA
infile 'somedata.data'
truncate INTO TABLE sometable
FIELDS TERMINATED BY "|" trailing nullcols
(
table_type,
table_code,
table_desc,
assoc_rec "nvl(:assoc_rec,'DUMMYVALUE')",
xdata "nvl(:xdata,'D')"
)
oracle@mutation#sqlldr scott/tiger control=somectl.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Sat May 20 05:37:54 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 10
oracle@mutation#sqlplus -s scott/tiger <<EOF
> set linesize 1000
> select * from sometable;
> exit;
> EOF
TABL TABLE_CODE TABLE_DESC ASSOC_REC X
---- ------------------ -------------------------------------------------- -------------------------------------------------- -
TF 0101.10.00.0 -Pure-bred breeding animals DUMMYVALUE D
TF 0101.90.10.0 --Race horses DUMMYVALUE D
TF 0101.90.20.0 --Other horses DUMMYVALUE D
TF 0101.90.90.0 --Other DUMMYVALUE D
TF 0102.10.00.0 -Pure-bred breeding animals DUMMYVALUE D
TF 0102.90.10.0 --Oxen DUMMYVALUE D
TF 0102.90.20.0 --Buffaloes DUMMYVALUE D
TF 0102.90.90.0 --Other DUMMYVALUE D
TF 0103.10.00.0 -Pure-bred breeding animals DUMMYVALUE D
TF 0103.91.00.0 --Weighing less than 50 kg DUMMYVALUE D
10 rows selected.
In any case, this table will give you a lot of trouble.
Your are indiscriminately using CHAR and not null all over.
Waste of storage and bad peformance.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
