Guest
09-23-2005, 12:58 PM
My department is starting a conversion from Informix to Oracle.
I am in the process of loading the unloaded data from Informix into
our Oracle database using SQLLDR. I have been able to load all of
our tables but one. Here is what the table schema looks like:
SQL> desc letter_bdy
Name Null? Type
----------------------------------------- -------- ---------------
PID NOT NULL NUMBER(38)
LETTER NOT NULL CHAR(10)
REVISE_DT NOT NULL DATE
SEL_ORDER NOT NULL NUMBER(38)
SEL NOT NULL CHAR(5)
ATTACHMENT NOT NULL NUMBER(38)
REQUIRED NOT NULL NUMBER(38)
NOTES CLOB
PARAGRAPH NOT NULL CLOB
USERNAME NOT NULL CHAR(10)
CREATE_DT NOT NULL TIMESTAMP(6)
My .dat file data looks something like this:
1771|86C|08/04/2003|18|Q|0|0|In fill-ins 41 use "correspondence",^M\
"inquiry", "return", "claim", etc.|We're sending your [41 14V] to
the Bureau of Alcohol, Tobacco ^M\
and Firearms, Tax Processing Center, P.O. Box 145433, ^M\
Cincinnati, OH 45250-5433 because:|abcdef11|2004-02-26
11:20:38|
1772|86C|08/04/2003|19|R|0|0||We're sending your Form W-4, Employee's
Withholding Allowance ^M\
Certificate, to the Internal Revenue Service office at the^M\
address shown at the end of this letter
because:|abcdef11|2004-02-26 11:22:01|
1773|86C|08/04/2003|20|S|0|0|In fill-in 42 use "correspondence",^M\
"inquiry", "return", "claim", etc.|We sent your [42 12V] return to
that office on [43 13D].|abcdef11|2004-02-26 11:2
2:43|
1774|86C|08/04/2003|21|T|0|0||Your account and records are kept at that
office. |abcdef11|2004-02-26 11:24:17|
1775|86C|08/04/2003|22|U|0|0||The Philadelphia Submission Processing
Center processes returns of^M\
U.S. citizens with foreign addresses or returns claiming an
^M\
exclusion from foreign earned income. Since you didn't file
your ^M\
return with Philadelphia, we will forward it for you.
However, this ^M\
will cause a delay in filing and Philadelphia may receive
your^M\
return late. |abcdef11|2004-02-26 11:24:48|
Here is a copy of my control file:
LOAD DATA
INFILE '86ctest.dat'
APPEND
INTO TABLE letter_bdy
FIELDS TERMINATED BY "|"
( pid,
letter,
revise_dt DATE(11) "mm/dd/yyyy",
sel_order,
sel,
attachment,
required,
notes CHAR(255) NULLIF (notes=BLANKS),
paragraph CHAR(4000),
username,
create_dt DATE "yyyy-mm-dd hh24:mi:ss"
)
I am getting these errors from my .log file:
SQL*Loader: Release 10.1.0.2.0 - Production on Fri Sep 23 12:41:29 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Control File: 86cbody2.ctl
Data File: 86ctest.dat
Bad File: 86ctest.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table LTR_BODY, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
PID FIRST * | CHARACTER
LETTER NEXT * | CHARACTER
REVISE_DT NEXT 11 | DATE
mm/dd/yyyy
SEL_ORDER NEXT * | CHARACTER
SEL NEXT * | CHARACTER
ATTACHMENT NEXT * | CHARACTER
REQUIRED NEXT * | CHARACTER
NOTES NEXT 255 | CHARACTER
NULL if NOTES = BLANKS
PARAGRAPH NEXT 4000 | CHARACTER
USERNAME NEXT * | CHARACTER
CREATE_DT NEXT * | DATE
yyyy-mm-dd hh24:m
i:ss
value used for ROWS parameter changed from 64 to 40
Record 1: Rejected - Error on table LTR_BODY, column PARAGRAPH.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table LTR_BODY, column REVISE_DT.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table LTR_BODY, column LETTER.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table LTR_BODY, column REVISE_DT.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table LTR_BODY, column USERNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table LTR_BODY, column LETTER.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 7: Rejected - Error on table LTR_BODY, column REVISE_DT.
Field in data file exceeds maximum length
I am new at using Oracle and SQLLDR. What am I doing wrong?
Thank you in advance.
Sincerly,
JD
I am in the process of loading the unloaded data from Informix into
our Oracle database using SQLLDR. I have been able to load all of
our tables but one. Here is what the table schema looks like:
SQL> desc letter_bdy
Name Null? Type
----------------------------------------- -------- ---------------
PID NOT NULL NUMBER(38)
LETTER NOT NULL CHAR(10)
REVISE_DT NOT NULL DATE
SEL_ORDER NOT NULL NUMBER(38)
SEL NOT NULL CHAR(5)
ATTACHMENT NOT NULL NUMBER(38)
REQUIRED NOT NULL NUMBER(38)
NOTES CLOB
PARAGRAPH NOT NULL CLOB
USERNAME NOT NULL CHAR(10)
CREATE_DT NOT NULL TIMESTAMP(6)
My .dat file data looks something like this:
1771|86C|08/04/2003|18|Q|0|0|In fill-ins 41 use "correspondence",^M\
"inquiry", "return", "claim", etc.|We're sending your [41 14V] to
the Bureau of Alcohol, Tobacco ^M\
and Firearms, Tax Processing Center, P.O. Box 145433, ^M\
Cincinnati, OH 45250-5433 because:|abcdef11|2004-02-26
11:20:38|
1772|86C|08/04/2003|19|R|0|0||We're sending your Form W-4, Employee's
Withholding Allowance ^M\
Certificate, to the Internal Revenue Service office at the^M\
address shown at the end of this letter
because:|abcdef11|2004-02-26 11:22:01|
1773|86C|08/04/2003|20|S|0|0|In fill-in 42 use "correspondence",^M\
"inquiry", "return", "claim", etc.|We sent your [42 12V] return to
that office on [43 13D].|abcdef11|2004-02-26 11:2
2:43|
1774|86C|08/04/2003|21|T|0|0||Your account and records are kept at that
office. |abcdef11|2004-02-26 11:24:17|
1775|86C|08/04/2003|22|U|0|0||The Philadelphia Submission Processing
Center processes returns of^M\
U.S. citizens with foreign addresses or returns claiming an
^M\
exclusion from foreign earned income. Since you didn't file
your ^M\
return with Philadelphia, we will forward it for you.
However, this ^M\
will cause a delay in filing and Philadelphia may receive
your^M\
return late. |abcdef11|2004-02-26 11:24:48|
Here is a copy of my control file:
LOAD DATA
INFILE '86ctest.dat'
APPEND
INTO TABLE letter_bdy
FIELDS TERMINATED BY "|"
( pid,
letter,
revise_dt DATE(11) "mm/dd/yyyy",
sel_order,
sel,
attachment,
required,
notes CHAR(255) NULLIF (notes=BLANKS),
paragraph CHAR(4000),
username,
create_dt DATE "yyyy-mm-dd hh24:mi:ss"
)
I am getting these errors from my .log file:
SQL*Loader: Release 10.1.0.2.0 - Production on Fri Sep 23 12:41:29 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Control File: 86cbody2.ctl
Data File: 86ctest.dat
Bad File: 86ctest.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table LTR_BODY, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
PID FIRST * | CHARACTER
LETTER NEXT * | CHARACTER
REVISE_DT NEXT 11 | DATE
mm/dd/yyyy
SEL_ORDER NEXT * | CHARACTER
SEL NEXT * | CHARACTER
ATTACHMENT NEXT * | CHARACTER
REQUIRED NEXT * | CHARACTER
NOTES NEXT 255 | CHARACTER
NULL if NOTES = BLANKS
PARAGRAPH NEXT 4000 | CHARACTER
USERNAME NEXT * | CHARACTER
CREATE_DT NEXT * | DATE
yyyy-mm-dd hh24:m
i:ss
value used for ROWS parameter changed from 64 to 40
Record 1: Rejected - Error on table LTR_BODY, column PARAGRAPH.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table LTR_BODY, column REVISE_DT.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table LTR_BODY, column LETTER.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table LTR_BODY, column REVISE_DT.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table LTR_BODY, column USERNAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table LTR_BODY, column LETTER.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 7: Rejected - Error on table LTR_BODY, column REVISE_DT.
Field in data file exceeds maximum length
I am new at using Oracle and SQLLDR. What am I doing wrong?
Thank you in advance.
Sincerly,
JD