Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12899: value too large for column string (actual: string, maximum: string) (10g)
ORA-12899: value too large for column string (actual: string, maximum: string) [message #399861] Fri, 24 April 2009 05:23 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Im hitting some error during insert operation using the sql below

insert into <table>
(select * from <table>


The problem is that all columns have same length and data type. I was browsing this forum and noticed this could be cause by character set issue. Is there any way to

1) Identify which column is causing this issue?
2) Any resolution for this issue?

Thanks in advance
Re: ORA-12899: value too large for column string (actual: string, maximum: string) [message #399872 is a reply to message #399861] Fri, 24 April 2009 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Check the definition of the tables
2) Increase the size of the columns or truncate the values

Regards
Michel
Re: ORA-12899: value too large for column string (actual: string, maximum: string) [message #399901 is a reply to message #399872] Fri, 24 April 2009 07:43 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Tq Michel, but if in a table there is close to hundred columns, is there any way to quickly identify which column is causing this error to get displayed?
Re: ORA-12899: value too large for column string (actual: string, maximum: string) [message #399903 is a reply to message #399901] Fri, 24 April 2009 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you had just check "describe" for the tables you'd already know the reason.
Do it, it is easy to verify line by line what are the differences in the types.

Regards
Michel
Re: ORA-12899: value too large for column string (actual: string, maximum: string) [message #399905 is a reply to message #399903] Fri, 24 April 2009 07:55 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
To be frank, theres no differents in data type.

Can character set differents be the issue here

The following query returns differents results in source and target database
SELECT * FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET%';


Re: ORA-12899: value too large for column string (actual: string, maximum: string) [message #399906 is a reply to message #399905] Fri, 24 April 2009 08:08 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
I will try to use the following error log provided by Oracle to identify which column is causing issue
SQL> create table error_log_dmlel_2
  2  (ora_err_number$ number, 
  3   ora_err_mesg$ varchar2(2000),
  4   ora_err_rowid$ rowid,   
  5   ora_err_optyp$ varchar2(2),
  6   ora_err_tag$ varchar2(2000) );
 
Table created.
 
declare i number;
begin
  i := 0;
  while i <= 10 loop
    insert into dmlel (pkey, field1, field2)
    values (i, i, i)
    LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;
    i := i+1;
  end loop;
end;
/ 
 
PL/SQL procedure successfully completed.
 
 
SQL> select ora_err_number$ num$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$ typ, pkey, field1 
  2  from error_log_dmlel
SQL> / 
 
      NUM$ ORA_ERR_MESG$                                      ORA_ERR_ROWID$            TYP PKEY FIEL
---------- -------------------------------------------------- ------------------------- --- ---- ----
     12899 ORA-12899: value too large for column "BULKLOAD"."                           I   10   10  
           DMLEL"."FIELD1" (actual: 2, maximum: 1)
 




If anyone have come across issue where similar error is thrown when all columns have similar datatype, please inform.

From my reading, think character set is the issue, but im not sure how character set can cause this problem

[Updated on: Fri, 24 April 2009 08:10]

Report message to a moderator

Re: ORA-12899: value too large for column string (actual: string, maximum: string) [message #399908 is a reply to message #399861] Fri, 24 April 2009 08:09 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't you think it might of been an idea to specify the fact that you're pulling data from a different database up front?

Different character sets can do this.
Re: ORA-12899: value too large for column string (actual: string, maximum: string) [message #399909 is a reply to message #399908] Fri, 24 April 2009 08:13 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Apologies for that, do you mind explaining how character set can cause this problem, maybe with a simple example for understanding. tq
Re: ORA-12899: value too large for column string (actual: string, maximum: string) [message #399914 is a reply to message #399909] Fri, 24 April 2009 08:26 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Globalization Support Guide

Regards
Michel
Previous Topic: alter query
Next Topic: PLS-00306: wrong number or types of arguments in call to 'GET_DNAME_USING_ID'
Goto Forum:
  


Current Time: Sat Dec 10 20:11:31 CST 2016

Total time taken to generate the page: 0.10044 seconds