Solved

ORA-12899: value too large for column "TGT_SCHEMA"."CUSTOMERS"."ZIPCODE"

Posted on 2013-05-11
12
1,086 Views
Last Modified: 2013-06-03
When trying to load data using following sql from source to target, I get the following error:

insert into tgt_schema.customer_tab
select
*
from
src_schema.customer_tab;


ERROR at line 3:
ORA-12899: value too large for column "TGT_SCHEMA"."CUSTOMER_TAB"."ZIPCODE"
(actual: 16, maximum: 10)

I altered the column data type in the target schema and changed the precision of zipcode to varchar2(32) and executed the above sql and the query ram fine.

Now I have to questions:

1. How to find the record in the source which exceeded varchar2(10)
2. How did the source contained this value when the precision in the source table is also     varchar2(10)?

Please advise.. please let me know if you need any other information

Thanks
0
Comment
Question by:gs79
[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
  • 4
  • 4
  • 4
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39158937
select * from "TGT_SCHEMA"."CUSTOMER_TAB"."ZIPCODE" where length(zipcode) > 10
0
 

Author Comment

by:gs79
ID: 39158958
Thanks sdstuber

I think you meant src_schema instead of tgt_schema:
select * from "SRC_SCHEMA"."CUSTOMER_TAB"."ZIPCODE" where length(zipcode) > 10

My other question is how did this record exist in the source as we have the same constraint in the source table too..Please let me know

Thanks..
0
 

Author Comment

by:gs79
ID: 39158962
Also I didnt see any record in the source where length>10

select count(*)
from "SRC_SCHEMA"."CUSTOMER_TAB"."ZIPCODE" where length(zipcode) > 10

returned 0

This is strange..

Thanks
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 74

Assisted Solution

by:sdstuber
sdstuber earned 150 total points
ID: 39158966
actually, I did mean the target schema

the only way it could exist is either:

 1 - you're mistaken about the source being constrained to 10 characters.

2 - there is a trigger on the target table and the error is arising from larger values being inserted than what you intended

3 - characters vs bytes - If the source system is defined as 10 characters where the characters are multi-byte and the target system is defined as 10 bytes, then an 8-character value in the source will be too big on the target.
0
 

Author Comment

by:gs79
ID: 39158986
This is the column definition in source and target after I do describe. I think we can rule out 2 since I ensured that there is no trigger.

The source and target were both defined as:

 VARCHAR2 (10 Byte)

I tried your above query on TGT_SCHEMA as well and there were no rows returned:

select count(*)
from "TGT_SCHEMA"."CUSTOMER_TAB"."ZIPCODE" where length(zipcode) > 10

Then is it option 3? With column definition as VARCHAR2(10 byte), is it not same that source and target has same definition?

I am still not able to find the culprit record

Please advice..

Thanks
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 total points
ID: 39159060
try lengthB

select * from "SRC_SCHEMA"."CUSTOMER_TAB"."ZIPCODE" where lengthB(zipcode) >= 16

I've used 16 due to this: actual: 16, maximum: 10
if 16 produces no results drop back the number perhaps.
0
 

Author Comment

by:gs79
ID: 39160062
Thanks PortletPaul..

I was able to find this out using lengthB..

This is how it looks  in src vs target:

select zipcode lengthb(zipcode), length(zipcode) from "SRC_SCHEMA"."CUSTOMER_TAB"."ZIPCODE" where lengthB(zipcode) >= 10

In the target (after I have modified the definition from varchar2(10 bytes) to 32 bytes

ZIPCODE      LENGTH_IN_BYTES      LENGTH_IN_CHAR
ïïïïïïïï              8                              8

Target:

ZIPCODE      LENGTH_IN_BYTES      LENGTH_IN_CHAR
ïïïïïïïï              16                              8

The same 8 character length zipcode is taking 16 byte in Target. The only difference is source is 10g and target is 11g.

The characterset value in Target is UTF8 and I am not able to find that information in Source yet.

Will it be the difference in characterset that is causing this problem. We have modeled the target tables same as source tables. Now if the characterset is different probably we have to change the definition..

Please advice..

Thanks,
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39160088
>>Will it be the difference in characterset that is causing this problem
probably, certainly looks like there's a difference

what is certain is that extended characters of UTF8 require more than 1 byte

the bigger question is: Should a zipcode accept UTF8 extended characters?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39160189
If the length in bytes is 16,  then the field can't be defined as varchar2(10 byte)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39160209
if the data is accepted at source as varchar(10) but that data expanded due to UTF8 at target, and this may be an idiot suggestion giving rise to mirth, but could (should?) the NLS_LANG be changed in the target at session level during the import?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39160212
oh sorry, I was looking at the output above backwards,  8 on target, 16 on source.

Nevermind

yes, changing character sets could cause the problem seen here
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39160224
this might help identify inconsistencies perhaps - run in both source & target

select
  d.parameter parameter
, d.value value
, i.value instance_value
, s.value session_value
from nls_database_parameters d
left join nls_instance_parameters i on d.parameter = i.parameter
left join nls_session_parameters s on d.parameter = s.parameter
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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

751 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