Solved

ORA-12899: value too large for column

Posted on 2011-03-09
16
1,826 Views
Last Modified: 2012-05-11
As to the length issues, these are fields with special characters in them.  When we unload them from Informix they are put in the file as a special character.  When you vi the file it appears as \nnn which is the octal representation of the character.  For example, in the cf_borr file we have an e-mail address that looks like:
\215firstnamefirstinitialoflastname\331@ci.waseca.mn.us
In Informix the length of this field is 49 because the two special characters are counted as one byte.  When we load this into Oracle we get the error:
                ORA-12899: value too large for column "RDDBA"."CF_BORR"."BORR_EMAIL_ADR" (actual: 51, maximum: 50)
In reality the value is only 49 characters with the 2 special characters (47 + 2 special = 49).  Maybe Oracle is counting the speical characters as two bytes (47 + 2*2 = 51)?  After looking at several of these data files this appears to be the most prevelant problem.  Any ideas?

TIA
N
0
Comment
Question by:Nakuru1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +2
16 Comments
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35085462
actually these characters are treated as a unicode character thats why thry take 2 byte of memory.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 50 total points
ID: 35085515
a character is a character.

whether it's an "A" or "3" or carriage return or something after the standard ascii values  (characters 128 and above)

if you need the 2 extra characters appended to 49 characters of "normal" data then you will need to extend your column

alter table yourtable modify (yourcolumn varchar2(51))
0
 

Author Comment

by:Nakuru1234
ID: 35085706
By the way, we have the environmental variable NLS_LANG set to “AMERICAN_AMERICA.WE8ISO8859P1”.  With this set the number of errors is reduced.  It appears that without this setting that the special characters are not being processed at all.


TIA,
N
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35085762
What is the character set of the database you are inserting into?

This sounds like you are having encoding issues.  You either aren't extracting the multi byte characters correctly or Oracle is trying to evaluate them incorrectly.
0
 

Author Comment

by:Nakuru1234
ID: 35085901
I'm inserting into Oracle 11G Database...

TIA,
N
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35086078
and?

Just saying 11g doesn't help us address your issues.

We need to know at a minimum:
The character set of the database you are inserting into.
The data as it looked in Informix before the extract.  In other words, did the extract mess/change the values?

My money is on a character set conversion problem.  We just need to know where the problem is occuring.
0
 
LVL 5

Assisted Solution

by:jaiminpsoni
jaiminpsoni earned 350 total points
ID: 35086846
If you are using multi byte characters, you need to change the NLS_LENGHT_SEMANTICS of your database.

The default value is byte. So when you create a table with say

BORR_EMAIL_ADR varchar2(50)

this means it defaults to 50 bytes.

If you have multibyte character coming in this field, it may not be able to store 50 characters.

So, you can modify your table like

BORR_EMAIL_ADR varchar2(50 char)

alter table CF_BORR modify (BORR_EMAIL_ADR varchar2(50 char));

This will make sure that it stores 50 characters irrespective of they are single byte of multibyte.

Let me know if you need more details.

Thanks,
Jaimin Soni
0
 
LVL 5

Accepted Solution

by:
jaiminpsoni earned 350 total points
ID: 35087165
0
 

Author Comment

by:Nakuru1234
ID: 35087334
I changed the character set on Oracle to match Informix....that should help.

TIA,
N
0
 

Author Comment

by:Nakuru1234
ID: 35087612
When I query the data from Informix I get the following on the screen:
borrower_email_ad+                                 (expression)
firstnamefirstinitialoflastnameÙ@ci.waseca.mn.us            49
The expression of 49 is the length.  Again, it is 47 “regular” characters plus the 2 special characters for a total of 49 characters.  These are not considered multi-byte characters as far as I know.  They are simply an extension of the ASCII character set (>127).  In order for it to be a multi-byte character we would have had to do some special set up within Informix to handle them, which we didn’t, so we never would have stored multi-byte characters.  That being the case they should be counted as one byte.  

When we set the environmental variable NLS_LANG set to “AMERICAN_AMERICA.WE8ISO8859P1” we end up loading some records with special characters that do not get loaded without it set.

Here is one of those records from Informix:
borrower_email_ad+                                 (expression)
wardvw wardvw_pchospital@yahoo.comÙ                         36

When I load it into Oracle with sqlldr and then query via the Enterprise Manager I get:
BORR_EMAIL_ADR       LENGTH(BORR_EMAIL_ADR)
wardvw wardvw_pchospital@yahoo.comÙ       48
It looks like the data is being loaded but why is the length 48?  Shouldn’t it still be 36?


TIA,
N
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 35087643
Check to see if trailing spaces were also loaded:

select BORR_EMAIL_ADR, LENGTH(trim(BORR_EMAIL_ADR)) from table;

if there are trailing spaces we need to figure out what is loading them.
0
 

Author Comment

by:Nakuru1234
ID: 35087954
I would expect it to return either 50 or 36.  With Informix the LENGTH function returns the length of a string with the trailing spaces taken out, thus the 36 even though it is defined as a fixed length of 50.  Either way, 48 is an odd length.

Here is the query I am using if anyone wants to play with it:
select BORR_EMAIL_ADR, length( BORR_EMAIL_ADR )
from cf_borr
where BORR_GLS_ID_KEY = 447695083512146;


TIA,
N


0
 

Author Comment

by:Nakuru1234
ID: 35094242
Hi Slightwv:

The output shows:
BORR_EMAIL_ADR       LENGTH(TRIM(BORR_EMAIL_ADR))
wardvw wardvw_pchospital@yahoo.comÙ       36

I guess there are trailing spaces in the database.  I’m not sure where they are coming from.  There are no trailing spaces in the load file.  Here’s what it looks like for this record:
447695083512146.0|423449498|T|N|O|30071|31|036|MT|MONTANA|PHILLIPS|01|HI-LINE RETIREMENT CENTER|2005-04-15 12:0
1:00|PO BOX 770| | |MALTA|MT|595380000|4066541190|5116|4066542876|0.00000000|wardvw \215wardvw_pchospital@yahoo
.com\331| |N|0|NO SUSPENSION|N|02|CORPORATION| |4|MALE-OWNED ORGANIZATION|Y|N|N|Y|N|Y|N|0|0|1|0|1|0|C|PROVIDED
BY CUSTOMER|1231| | || |N|VANWICHEN, WARD C.|4066541190|4066542876|RETIREMENT & ASSISTED LIVING| | |NP|NON-PROF
IT|0|| |Y|2008-04-30 12:01:00||||||||| | |0.0|0.0||0.0|0.0||||614266042|N|NON-FAITH|0|NO RELATIONSHIP|4464|

TIA,
N.
0
 

Author Comment

by:Nakuru1234
ID: 35094347
Just another strange thing.  I ran the following SQL:
select BORR_EMAIL_ADR ,
LENGTH( BORR_EMAIL_ADR ),
LENGTH( TRIM( BORR_EMAIL_ADR ) )
from rddba.cf_borr
where LENGTH( BORR_EMAIL_ADR ) != 50
;
Wouldn’t you know, the only records returned were the ones with multiple special characters?
BORR_EMAIL_ADR       LENGTH(BORR_EMAIL_ADR)      LENGTH(TRIM(BORR_EMAIL_ADR))
wardvw wardvw_pchospital@yahoo.comÙ       48      36
betty lbburks@volfirst.netÙ       48      28
Danny Neal judgeneal@hotmail.comÙ       48      34
Kim Santman, VP Finance KSantman@aboutsmh.orgÙ       48      47
Steve Adams sadams@arroyogrande.orgÙ       48      37
Yuliya Zingertal yuliyaz@cresource.orgÙ       48      40
Harlan L. Gronewold halagron@iowatelecom.netÙ       48      46
Brett Cook christopherbrett@yahoo.comÙ       48      39

This is very strange behavior. Any ideas?

TIA,
N

0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 35094461
What is the datatype for that column in Oracle?

How is the column defined in the sqlLoader control file?

In Oracle CHAR columns are fixed width (padded).

Check out the following below.
drop table tab1 purge;
create table tab1(col1 char(10));
insert into tab1 values('a');
select length(col1), length(trim(col1)) from tab1;

select length(col1), length(trim(col1)) from tab1;
 10                  1

Open in new window

0
 

Author Comment

by:Nakuru1234
ID: 35128231
Please add this question to my question history...

TIA,
N
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question