Solved

ORA-12899: value too large for column

Posted on 2011-03-09
16
1,738 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
  • 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 73

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
 
LVL 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 46
Oracle SQL Select Statement 19 58
automatic email alert 1 21
Best RAID for a BDD Oracle 4 18
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now