Home » RDBMS Server » Server Utilities » sql loader error (oracle 9201)
sql loader error [message #276644] Thu, 25 October 2007 15:23 Go to next message
lawlade
Messages: 59
Registered: May 2007
Location: LAUREL, MARYLAND
Member
LOAD DATA
INFILE *
Truncate
INTO TABLE nationalorder_sku_order
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(OrderID_pk integer external,
SKU_ID integer external,
Lang nullif lang=blanks,
Quantity integer external,
FirstName,
LastName,
JobTitle nullif JobTitle=blanks,
POLevel nullif POLevel=blanks,
Address1,
Address2 nullif Address2=blanks,
City,
State,
Zip,
BusinessPhone,
onBehalfOf nullif onBehalfof=blanks,
OrderedDate "to_date(substr(:OrderedDate, 1, instr(:OrderedDate, '.', 1, 1) -1),'YYYY-MM-DD HH24:MI:SS')",
BatchedDate "to_date(substr(:BatchedDate, 1, instr(:BatchedDate, '.', 1, 1) -1),'YYYY-MM-DD HH24:MI:SS')")
begindata
1,168,"",1,"Debbie","Dudley","","","951 W Bethel Rd","","Coppell","TX","75099-9671","972-393-6087","","2003-08-01 09:31:18.687","2003-08-01 18:05:57.0"

Record 1: Rejected - Error on table NATIONALORDER_SKU_ORDER, column LANG.
ORA-01401: inserted value too large for column

Can anyone help with this error? Thanks Mad
Re: sql loader error [message #276646 is a reply to message #276644] Thu, 25 October 2007 16:09 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:

ORA-01401: inserted value too large for column


Check the size of columns and the values you want to insert.

Quote:

ORA-01401: inserted value too large for column
Cause: The value entered is larger than the maximum width defined for the column.
Action: Enter a value smaller than the column width or use the MODIFY option with
ALTER TABLE to expand the column width.
Re: sql loader error [message #276687 is a reply to message #276644] Fri, 26 October 2007 00:24 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

Record 1: Rejected - Error on table NATIONALORDER_SKU_ORDER, column LANG.
ORA-01401: inserted value too large for column

When you look at the rejected row, you see two doublequotes in the position of the LANG column (third "column"; as the post is NOT formatted, it may be four single quotes as well, but I suppose the first variant).

If you want to treat as an empty string, you shall add OPTIONALLY ENCLOSED BY clause into the FIELDS specification, so it would be
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
Previous Topic: Sequence Issues After Export / Import
Next Topic: SQL Loader and partitioned indexes
Goto Forum:
  


Current Time: Wed Dec 07 16:33:54 CST 2016

Total time taken to generate the page: 0.10819 seconds