| using sqlldr i am not able to add data more than 255 character in Varchar2(4000) defined field [message #167580] |
Fri, 14 April 2006 02:12  |
prasadanu
Messages: 3
Registered: April 2006
Location: Hyderabad
|
Junior Member |
|
|
I have a problem..... like.... in a Table i have a column type Varchar2(4000) and when i am using sqlldr and adding the values.... its not giving error and not adding that row while if that field have more than 255 characters.....
how can i over come this issue....
here i am giving the error what i am getting for that....
Record 6: Rejected - Error on table TMPCOURSEFULL, column TLEDEF_DESC.
Field in data file exceeds maximum length
Please Help me regarding this...
Thanks
PRasad Anu
|
|
|
|
|
|
| Re: using sqlldr i am not able to add data more than 255 character in Varchar2(4000) defined field [message #167589 is a reply to message #167587] |
Fri, 14 April 2006 04:08  |
prasadanu
Messages: 3
Registered: April 2006
Location: Hyderabad
|
Junior Member |
|
|
Hi Frank Naude,
Here is my ctl file details where i have to add this....
-----
Load Data INFILE 'D:\Sumtotal Migration tool\Data\courses.txt' TRUNCATE INTO TABLE tmpCourseFull FIELDS TERMINATED BY '|' TRAILING NULLCOLS (tLEDef_CD, tLEDef_NAME, tLEDef_LEMTD, tLEDef_StatusName, tLEDef_Vendor, tLEDef_PayTerm, tLEDef_MedType, tLEDef_ConType, tLEDef_Desc , tLEDef_BaseCost, tLEDef_Esthrs, tLEDef_EstCrdtHrs, tLEDef_CnclBusDays, tLEDef_MinPctGrd, tLEDef_InstNotes, tLEDef_EmpNotes, tLEDef_URL, tLEDef_ReqApprInd, tLEDef_DefApprEmpNo, tLEDef_Currency, tLEDef_DefInstNo, tLEDef_AutoAttmpInd, tLEDef_CBTLaunchMtd, tLEDef_CBTPath,
tLEDef_MaxAttmp, tLEDef_ActiveInd, tLEDef_CertificationName, tLEDef_ExpParamValue, tLEDef_DomainCd, tLEDef_ActType)
-----
problem with tLEDef_Desc and TLEDEF_EMPNOTES
Please Help me reagarding this....
Thanks
Prasad Anu
|
|
|
|
|
|
|
|
|
|
| Re: using sqlldr i am not able to add data more than 255 character in Varchar2(4000) defined field [message #180903 is a reply to message #180899] |
Wed, 05 July 2006 15:17 |
swamy99
Messages: 30
Registered: June 2006
|
Member |
|
|
Thanks for the info. But my problem is when I load the data, I am loading it into multiple tables.
As suggested I used char(2000) for that field. Now the error log file is not showing the error for that table, but the field data which I am using as filler in other tables, is the one that is giving the "Field in data file exceeds maximum length" which infact has no such column (that is the reason I am using as filler) in those tables.
Is there anything that I need to specify for the fields where I am using filler?
|
|
|
|
|
|
| Re: using sqlldr i am not able to add data more than 255 character in Varchar2(4000) defined field [message #180911 is a reply to message #180903] |
Wed, 05 July 2006 15:44 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is an example of what I think you are talking about:
If you have data like this (test.dat), where the first column is 260 characters:
abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz,b,
and you have a table like this:
CREATE TABLE test_tab (test_col CLOB)
/
and you load your data like this:
HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
if you are using the following control file (test.ctl), it reproduces your error:
LOAD DATA
INFILE 'test.dat'
INTO TABLE test_tab
FIELDS TERMINATED BY ','
(filler1 FILLER,
test_col CHAR (2000))
but the following loads the data without error:
LOAD DATA
INFILE 'test.dat'
INTO TABLE test_tab
FIELDS TERMINATED BY ','
(filler1 FILLER CHAR (2000),
test_col CHAR (2000))
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
