| need help with sql-loader [message #134573] |
Thu, 25 August 2005 10:39 |
shaheb
Messages: 1
Registered: August 2005
|
Junior Member |
|
|
Following are details for batch/logs/errors/data
Table info:-
============
SQL> desc msag;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
MPREDIR CHAR(2)
MSN VARCHAR2(60)
MSTSUFF CHAR(4)
MPOSTDIR CHAR(2)
MLR NUMBER(10)
MHR NUMBER(10)
MCOM VARCHAR2(32)
MST CHAR(2)
MOE CHAR(1)
MESN VARCHAR2(5)
MDC VARCHAR2(6) Y
MINIT VARCHAR2(3) Y
MPSAPID VARCHAR2(4) Y
MCTYID CHAR(4)
MEXCH CHAR(4)
MGEN VARCHAR2(20) Y
MTAR CHAR(6)
MRES VARCHAR2(27) Y
MSAG_ORIGIN VARCHAR2(2) Y
Control file:-
===============
LOAD DATA
INFILE 'MSAG2300.DAT'
APPEND INTO TABLE msag
(
MPREDIR POSITION(1:2) CHAR(2) ,
MSN POSITION(3:62) VARCHAR(60) ,
MSTSUFF POSITION(63:66) CHAR(4) ,
MPOSTDIR POSITION(67:68) CHAR(2) ,
MLR POSITION(69:78) INTEGER(10) ,
MHR POSITION(79:88) INTEGER(10) ,
MCOM POSITION(89:120) VARCHAR(32) ,
MST POSITION(121:122) CHAR(2) ,
MOE POSITION(123:123) CHAR(1) ,
MESN POSITION(124:128) VARCHAR(5) ,
MDC POSITION(129:134) VARCHAR(6) ,
MINIT POSITION(200:202) VARCHAR(3) ,
MPSAPID POSITION(135:138) VARCHAR(4) ,
MCTYID POSITION(139:142) CHAR(4) ,
MEXCH POSITION(143:146) CHAR(4) ,
MGEN POSITION(147:166) VARCHAR(20) ,
MTAR POSITION(167:172) CHAR(6) ,
MRES POSITION(173:199) VARCHAR(27)
)
BATCH FILE:-
============
SQLLDR USERID=scott/tiger@myoracle,
CONTROL='C:\oracle\ora92\bin\C1\4MSAG2300.ctl',
LOG='C:\oracle\ora92\bin\C1\4MSAG2300.log',
BAD='C:\oracle\ora92\bin\C1\4MSAG2300.bad',
DATA='C:\oracle\ora92\bin\C1\MSAG2300.DAT',
rows=10000,errors=10000
DATA FILE:-
===========
Its 203+NewLine=204 byte fix length data
ABBEYWOOD PL 2501 25
44 LEXINGTON KYB100 051705 FAYELXSE
05172005TF *
ABBEYWOOD RD 2204 24
08 LEXINGTON KYB100 030205 FAYELXSE
03022005 *
ABBINGTON HILL 1700 17
53 LEXINGTON KYB100 030205 FAYELXEK
03022005 *
LOG FILE:-
============
SQL*Loader: Release 9.2.0.1.0 - Production on Thu Aug 18 11:26:47 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL*Loader-307: Warning: conflicting lengths 60 and 62 specified for column MSN table GLIDDEN.E911_MSAG
SQL*Loader-307: Warning: conflicting lengths 32 and 34 specified for column MCOM table GLIDDEN.E911_MSAG
SQL*Loader-307: Warning: conflicting lengths 5 and 7 specified for column MESN table GLIDDEN.E911_MSAG
SQL*Loader-307: Warning: conflicting lengths 6 and 8 specified for column MDC table GLIDDEN.E911_MSAG
SQL*Loader-307: Warning: conflicting lengths 3 and 5 specified for column MINIT table GLIDDEN.E911_MSAG
SQL*Loader-307: Warning: conflicting lengths 4 and 6 specified for column MPSAPID table GLIDDEN.E911_MSAG
SQL*Loader-307: Warning: conflicting lengths 20 and 22 specified for column MGEN table GLIDDEN.E911_MSAG
SQL*Loader-307: Warning: conflicting lengths 27 and 29 specified for column MRES table GLIDDEN.E911_MSAG
Control File: C:\oracle\ora92\bin\C1\4MSAG2300.ctl
Data File: C:\oracle\ora92\bin\C1\MSAG2300.DAT
Bad File: C:\oracle\ora92\bin\C1\4MSAG2300.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 10000
Bind array: 10000 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table MSAG, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MPREDIR 1:2 2 CHARACTER
MSN 3:62 62 VARCHAR
MSTSUFF 63:66 4 CHARACTER
MPOSTDIR 67:68 2 CHARACTER
MLR 69:78 10 INTEGER
MHR 79:88 10 INTEGER
MCOM 89:120 34 VARCHAR
MST 121:122 2 CHARACTER
MOE 123:123 1 CHARACTER
MESN 124:128 7 VARCHAR
MDC 129:134 8 VARCHAR
MINIT 200:202 5 VARCHAR
MPSAPID 135:138 6 VARCHAR
MCTYID 139:142 4 CHARACTER
MEXCH 143:146 4 CHARACTER
MGEN 147:166 22 VARCHAR
MTAR 167:172 6 CHARACTER
MRES 173:199 29 VARCHAR
Record 1: Rejected - Error on table MSAG, column MSN.
Variable length field exceeds maximum length.
Record 2: Rejected - Error on table MSAG, column MSN.
Variable length field exceeds maximum length.
|
|
|
|
|
|
When i change control file from varchar to Char, it recognize correct length of field
but then complains fo illegal conversion of datatype
Table MSAG, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MPREDIR 1:2 2 CHARACTER
MSN 3:62 60 CHARACTER
MSTSUFF 63:66 4 CHARACTER
MPOSTDIR 67:68 2 CHARACTER
MLR 69:78 10 INTEGER
MHR 79:88 10 INTEGER
MCOM 89:120 32 CHARACTER
MST 121:122 2 CHARACTER
MOE 123:123 1 CHARACTER
MESN 124:128 5 CHARACTER
MDC 129:134 6 CHARACTER
MINIT 200:202 3 CHARACTER
MPSAPID 135:138 4 CHARACTER
MCTYID 139:142 4 CHARACTER
MEXCH 143:146 4 CHARACTER
MGEN 147:166 20 CHARACTER
MTAR 167:172 6 CHARACTER
MRES 173:199 27 CHARACTER
Record 1: Rejected - Error on table MSAG.
ORA-01460: unimplemented or unreasonable conversion requested
Record 2: Rejected - Error on table MSAG.
ORA-01460: unimplemented or unreasonable conversion requested
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
