Home » SQL & PL/SQL » SQL & PL/SQL » Difficulty in resolving Date ORA-12899: value too large for column (merged) (Oracle Database Version : 10.2.0.3.0 Oracle Client Version : 10.2.0.1.0 Oracle DB OS - Win2003 R2 E x64. Oracle client OS - Windows 2003 R2 E x32 JDK version : 1.4 )
Difficulty in resolving Date ORA-12899: value too large for column (merged) [message #439498] Sun, 17 January 2010 22:48 Go to next message
anjali.nalapure
Messages: 2
Registered: January 2010
Location: Pune
Junior Member
Hi,
I am having great difficulty to resolve the following error for insert of date column in DB : java.sql.SQLException: ORA-12899: value too large for column

Oracle Database Version : 10.2.0.3.0
Oracle Client Version : 10.2.0.1.0
Oracle DB OS - Win2003 R2 E x64.
Oracle client OS - Windows 2003 R2 E x32
JDK version : 1.4

We have our software running on multile m/cs. Most of the places its working fine. But on one m/c, we are facing issue for the same code and same database.
On that m/c, 1000 sqls are executed successfully, but some times one of them fails and gives below error. This issue is very hard to reproduce. It comes once in a while but it has affected our software quality a lot.

Date is 'Wed Sep 30 00:00:00 CEST 2009'

Error is :-
java.sql.SQLException: ORA-12899: value too large for column "PRD_7"."SUMMARY_TABLE"."PERIOD_END_DT" (actual: 40, maximum: 7)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:671)
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:597)
at oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe(T2CPreparedStatement.java:570)
at oracle.jdbc.driver.T2CPreparedStatement.executeForRows(T2CPreparedStatement.java:763)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1159)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3284)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3367)

This problem has really show stopper to us and I cannot see how to resolve it.

Can someone, please help me?

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column [message #439501 is a reply to message #439498] Sun, 17 January 2010 22:56 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
ORA-12899: value too large for column string (actual: string, maximum: string)

Cause: An attempt was made to insert or update a column with a value which is too wide for the width of the destination column. The name of the column is given, along with the actual width of the value, and the maximum allowed width of the column. Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are reported in bytes.

Action: Examine the SQL statement for correctness. Check source and destination column data types. Either make the destination column wider, or use a subset of the source column (i.e. use substring).


sriram Smile

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column [message #439502 is a reply to message #439498] Sun, 17 January 2010 22:57 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
ORA-12899: value too large for column string (actual: string, maximum: string)
Cause: An attempt was made to insert or update a column with a value which is too wide for the width of the destination column. The name of the column is given, along with the actual width of the value, and the maximum allowed width of the column. Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are reported in bytes.

Action: Examine the SQL statement for correctness. Check source and destination column data types. Either make the destination column wider, or use a subset of the source column (i.e. use substring).


can you post details of your SUMMARY_TABLE?

you can find this by typing
DESC SUMMARY_TABLE

in SQL*Plus client

[Updated on: Sun, 17 January 2010 23:00]

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column [message #439504 is a reply to message #439501] Sun, 17 January 2010 23:08 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
of Which format you are inserting into that date column....
miss read the post ..."Date is Wed Sep 30 00:00:00 CEST 2009" into which column what data type it has?

sriram Smile

[Updated on: Sun, 17 January 2010 23:23]

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column [message #439505 is a reply to message #439498] Sun, 17 January 2010 23:10 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
 
SQL> create table  test (col1 char(1));

Table created.

SQL> select column_name, data_length, char_length from user_tab_columns where table_name ='TEST';

COLUMN_NAME                    DATA_LENGTH CHAR_LENGTH
------------------------------ ----------- -----------
COL1                                     1           1

SQL> insert into test values('A');

1 row created.

SQL> insert into test values('AB');
insert into test values('AB')
                        *
ERROR at line 1:
ORA-12899: value too large for column "XXMITG"."TEST"."COL1" (actual: 2,
maximum: 1)


hope this example helps you Smile

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column [message #439506 is a reply to message #439504] Sun, 17 January 2010 23:22 Go to previous messageGo to next message
anjali.nalapure
Messages: 2
Registered: January 2010
Location: Pune
Junior Member
My date format is yyyyMMddHHmmss. However this works fine on same server always. sometimes it fails and its very hard to reproduce.

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column [message #439507 is a reply to message #439498] Sun, 17 January 2010 23:40 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
anjali.nalapure wrote on Sun, 17 January 2010 22:50
Error is :-
java.sql.SQLException: ORA-12899: value too large for column "PRD_7"."SUMMARY_TABLE"."PERIOD_END_DT" (actual: 40, maximum: 7)


Can you please post the DDL for this table and the actual value you are trying to insert

So we expect DDL and actual insert query before being executed.

-Rahul

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column [message #439508 is a reply to message #439506] Sun, 17 January 2010 23:47 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
anjali.nalapure wrote on Mon, 18 January 2010 10:52
My date format is yyyyMMddHHmmss. However this works fine on same server always. sometimes it fails and its very hard to reproduce.


SQL> select length('Wed Sep 30 00:00:00 CEST 2009') from dual;
LENGTH('WEDSEP3000:00:00CEST2009')
----------------------------------
                                29
SQL> select length(TO_CHAR(sysdate, 'yyyyMMddHHmmss')) from dual;

LENGTH(TO_CHAR(SYSDATE,'YYYYMMDDHHMMSS'))
-----------------------------------------
                                       14
SQL>


Quote:
Error is :-
(actual: 40, maximum: 7)


What is that 40 length data you are tring to insert ?
You are inserting 40 length in to 7 length field...which you should n`t.

sriram Smile

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column [message #439520 is a reply to message #439506] Mon, 18 January 2010 02:19 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
anjali.nalapure wrote on Mon, 18 January 2010 06:22
My date format is yyyyMMddHHmmss. However this works fine on same server always. sometimes it fails and its very hard to reproduce.

Sounds to me like your java program does not use a Date as bind parameter, but a String.
This would imply your error is in the java code (it should use the proper datatypes, so invalid values can be checked where they ought to be checked)

[Edit: hm, considering the error message, your database-column seems to be of a VARCHAR2 type as well, instead of a DATE]

[Updated on: Mon, 18 January 2010 02:21]

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column (merged) [message #439522 is a reply to message #439498] Mon, 18 January 2010 02:27 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
can you please post a describe of SUMMARY_TABLE table as earlier requested

Report message to a moderator

Re: Difficulty in resolving Date ORA-12899: value too large for column (merged) [message #439527 is a reply to message #439522] Mon, 18 January 2010 02:35 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
ayush_anand wrote on Mon, 18 January 2010 09:27
can you please post a describe of SUMMARY_TABLE table as earlier requested

What's the rush? You don't know where the original poster lives; might very well be somewhere in the world where it is the middle of the night!

Report message to a moderator

Previous Topic: truncate and insert
Next Topic: view create replace performance issue
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Aug 05 11:11:02 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.