Home » RDBMS Server » Server Utilities » SQLLOADER value too large for column (Oracle DB 11.2.0.3 OS: Windows 7)
SQLLOADER value too large for column [message #593705] Tue, 20 August 2013 03:58 Go to next message
Flyby
Messages: 148
Registered: March 2011
Location: Belgium
Senior Member
Hello,
First time using sql loader for loading large files.
Can't get it to work with even a simple file
Error:
Record 1: Rejected - Error on table DP, column SPECIFICATION.
ORA-12899: value too large for column "ROPAT"."DP"."SPECIFICATION" (actual: 23, maximum: 20)

Control file:
OPTIONS (ERRORS=0,SKIP=0)
load data
 infile *
 into table DP
 fields terminated by "|"
 TRAILING NULLCOLS
 ( SESSIONTYPE, SPECIFICATION)
begindata
Backup|Dro_IDB_Daily_Full_1300|


Table defintion
CREATE TABLE DP
(
  SESSIONTYPE    VARCHAR2(20 CHAR),
  SPECIFICATION  VARCHAR2(20 CHAR)
)
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


Log:
SQL*Loader: Release 11.2.0.3.0 - Production on Tue Aug 20 10:51:00 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   sl.txt
Data File:      sl.txt
  Bad File:     sl.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 0
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table DP, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SESSIONTYPE                         FIRST     *   |       CHARACTER            
SPECIFICATION                        NEXT     *   |       CHARACTER            

Record 1: Rejected - Error on table DP, column SPECIFICATION.
ORA-12899: value too large for column "ROPAT"."DP"."SPECIFICATION" (actual: 23, maximum: 20)


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table DP:
  0 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Tue Aug 20 10:51:00 2013
Run ended on Tue Aug 20 10:51:01 2013

Elapsed time was:     00:00:00.18
CPU time was:         00:00:00.06

[Updated on: Tue, 20 August 2013 04:00]

Report message to a moderator

Re: SQLLOADER value too large for column [message #593706 is a reply to message #593705] Tue, 20 August 2013 04:03 Go to previous messageGo to next message
Flyby
Messages: 148
Registered: March 2011
Location: Belgium
Senior Member
Found it: SPECIFICATION should be bigger
Re: SQLLOADER value too large for column [message #593707 is a reply to message #593705] Tue, 20 August 2013 04:03 Go to previous messageGo to next message
gazzag
Messages: 388
Registered: November 2010
Location: Bristol, UK
Senior Member
What does your data look like?
Re: SQLLOADER value too large for column [message #593708 is a reply to message #593705] Tue, 20 August 2013 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59977
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is not clear in "ORA-12899: value too large for column "ROPAT"."DP"."SPECIFICATION" (actual: 23, maximum: 20)"?

Regards
Michel
Re: SQLLOADER value too large for column [message #593721 is a reply to message #593707] Tue, 20 August 2013 06:10 Go to previous messageGo to next message
Flyby
Messages: 148
Registered: March 2011
Location: Belgium
Senior Member
You may close the topic Michel, I was not awake and mixing up another sql loader oddity
Re: SQLLOADER value too large for column [message #593723 is a reply to message #593721] Tue, 20 August 2013 06:14 Go to previous message
Michel Cadot
Messages: 59977
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, this was then really a too large value.

Regards
Michel
Previous Topic: Syntax error in using query parameter in expdp
Next Topic: SQL loader
Goto Forum:
  


Current Time: Thu Dec 18 04:59:18 CST 2014

Total time taken to generate the page: 0.05458 seconds