• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1988
  • Last Modified:

ORA-12899: value too large for column

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
Nakuru1234
Asked:
Nakuru1234
  • 8
  • 4
  • 2
  • +2
5 Solutions
 
mayankagarwalCommented:
actually these characters are treated as a unicode character thats why thry take 2 byte of memory.
0
 
sdstuberCommented:
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
 
Nakuru1234Author Commented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
slightwv (䄆 Netminder) Commented:
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
 
Nakuru1234Author Commented:
I'm inserting into Oracle 11G Database...

TIA,
N
0
 
slightwv (䄆 Netminder) Commented:
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
 
jaiminpsoniCommented:
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
 
jaiminpsoniCommented:
0
 
Nakuru1234Author Commented:
I changed the character set on Oracle to match Informix....that should help.

TIA,
N
0
 
Nakuru1234Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Nakuru1234Author Commented:
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
 
Nakuru1234Author Commented:
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
 
Nakuru1234Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Nakuru1234Author Commented:
Please add this question to my question history...

TIA,
N
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now