Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01801: date format is too long for internal buffer (ORACLE Version 11.1.0.6.0,32 Bit Windows)
ORA-01801: date format is too long for internal buffer [message #444162] Fri, 19 February 2010 00:56 Go to next message
baulraj
Messages: 48
Registered: January 2009
Member
SQL> select * from V$version;
BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production          
PL/SQL Release 11.1.0.6.0 - Production                                          
CORE	11.1.0.6.0	Production                                                      
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production         NLSRTL Version 11.1.0.6.0 - Production                                          
SQL> SELECT TO_CHAR(PA_TIME,'DD MON YYYY HH24:MI:SS') FROM APATS WHERE counter=46828;

TO_CHAR(PA_TIME,'DDM                                                            
--------------------                                                            
00 000 0000 00:00:00                                                         

In my stored procedure, i am assigning the above query value to a varchar variable raises the below error.
ERROR at line 1:
ORA-01801: date format is too long for internal buffer 
ORA-06512: at "RWH_AUDIT_TEST.SP_APATS", line 905 
ORA-06512: at "RWH_AUDIT_TEST.SP_EXECUTE_AUDIT_SUMM", line 20 
ORA-06512: at line 1 

Can anyone tell me is this a ORACLE date type bug like in ORACLE 10.2 version ?
How can i solve this?
Re: ORA-01801: date format is too long for internal buffer [message #444166 is a reply to message #444162] Fri, 19 February 2010 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Point 1/ No relation with an Oracle bug as you enter an invalid date (it is a bug in your program)
Point 2/ Bugs on invalid date and its consequences have been reported since many years; I don't think it is a priority for Oracle, I rather think they will never be fixed.

Regards
Michel
Re: ORA-01801: date format is too long for internal buffer [message #444169 is a reply to message #444166] Fri, 19 February 2010 01:11 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi Michel,
Thanks for your reply.
SQL> SELECT pa_time FROM APATS WHERE counter=46828;

PA_TIME  
---------                                                      
00-DECEMB

in TOAD 
SELECT pa_time FROM APATS WHERE counter=46828
12/30/1899 1:30:00 PM

To_CHAR(PA_TIME) conversion gives 00 000 0000 00:00:00 as output.
I agree with you that there is a bug in code. can you please help me to find what is wrong in this statement?

Regards
Baulraj.V


Re: ORA-01801: date format is too long for internal buffer [message #444171 is a reply to message #444169] Fri, 19 February 2010 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing is wrong.
With invalid dates you have unpredictable result.

Regards
Michel

[Updated on: Fri, 19 February 2010 01:14]

Report message to a moderator

Re: ORA-01801: date format is too long for internal buffer [message #444172 is a reply to message #444171] Fri, 19 February 2010 01:18 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
SQL> SELECT pa_time FROM APATS WHERE counter=46828;

PA_TIME  
---------                                                      
00-DECEMB

in TOAD 
SELECT pa_time FROM APATS WHERE counter=46828
12/30/1899 1:30:00 PM

SQL> SELECT TO_CHAR(PA_TIME,'DD MON YYYY HH24:MI:SS') FROM APATS WHERE counter=46828;

TO_CHAR PA_TIME,'DDM        
--------------------                                        
00 000 0000 00:00:00 

Is this date value '12/30/1899 1:30:00 PM' is invalid?

Thanks&Regards
Baulraj.V
Re: ORA-01801: date format is too long for internal buffer [message #444173 is a reply to message #444172] Fri, 19 February 2010 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the translation of the invalid date in this case, with this environment and this tool.

Regards
Michel
Re: ORA-01801: date format is too long for internal buffer [message #444182 is a reply to message #444172] Fri, 19 February 2010 02:27 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
see this test case
Sql+>create table orafaq_date(x date);

Table created.

Sql+>insert into orafaq_date values(reverse(sysdate));

1 row created.

Sql+>desc orafaq_date
           Name                                        Null?    Type
           ------------------------------------------- -------- --------
    1      X                                                    DATE

Sql+>select * from orafaq_date;

X
---------
13-WE8MSW

Sql+>select to_char(x,'DD MON YYYY HH24:MI:SS') FROM orafaq_date;

TO_CHAR(X,'DDMONYYYY
--------------------
00 000 0000 00:00:00
In this case its not Bug...But How we did our transaction.
Sql+>

case 1)
It all depends how you got that date in some binary format?
case 2)
do have any column value like '00-000-00' in the inter tables of database..

Make sure you have a backup.And contact Oracle support before this...
http://www.oraclelog.com/2009/07/20/oracle/ora-39125-ora-01801-date-format-is-too-long-for-internal-buffer/

sriram Smile
Re: ORA-01801: date format is too long for internal buffer [message #444185 is a reply to message #444162] Fri, 19 February 2010 03:12 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi Sriram,
Thanks for your reply.
I am not going to contact ORACLE support at this moment, instead i will add a condition to the query to handle this exceptional case.

Regards
Baulraj.V
Re: ORA-01801: date format is too long for internal buffer [message #444186 is a reply to message #444185] Fri, 19 February 2010 03:29 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
It`s your wish.
But i never see such coding & dezine which may store date in unknown format..
Goodluck
sriram Smile
Re: ORA-01801: date format is too long for internal buffer [message #444188 is a reply to message #444185] Fri, 19 February 2010 03:54 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
baulraj wrote on Fri, 19 February 2010 09:12
Hi Sriram,
Thanks for your reply.
I am not going to contact ORACLE support at this moment, instead i will add a condition to the query to handle this exceptional case.

Regards
Baulraj.V


A far better idea would be to find the bit of code that's inserting invalid dates in the first place and fix that. Then update all the invalid dates in your db to valid ones (or null).
Re: ORA-01801: date format is too long for internal buffer [message #444190 is a reply to message #444188] Fri, 19 February 2010 04:11 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes that would be better..
Quote:
case 1)
It all depends how you got that date in some binary format?
case 2)
do have any column value like '00-000-00' in the inter tables of database..

If it on dictionary table you should really contact Oracle support.
sriram Smile
Re: ORA-01801: date format is too long for internal buffer [message #444191 is a reply to message #444186] Fri, 19 February 2010 04:15 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

i solved the problem by checking the charecter value before assigning to the variable. I just added the following code in the SP.

if TO_CHAR(PA_TIME_V,'DD MON YYYY HH24:MI:SS')<> 
'00 000 0000 00:00:00' then
LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TIME_N||CHR(164)||TO_CHAR(PA_TIME_V,'DD MON YYYY HH24:MI:SS');
end if;


Thank you very much to all.

Regards
Baulraj.V



Re: ORA-01801: date format is too long for internal buffer [message #444193 is a reply to message #444191] Fri, 19 February 2010 04:27 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
congrats ...
Which may not give the accurate values...
You are hiding or not selecting them.
instead of that try to fix the code which is inserting the wrong type of date values into the table.
sriram Smile
Re: ORA-01801: date format is too long for internal buffer [message #444194 is a reply to message #444193] Fri, 19 February 2010 04:43 Go to previous message
baulraj
Messages: 48
Registered: January 2009
Member
Hi Sriram,

I do agree with you. i will surely fix the code where it is wrongly inserting a invalid date value.I will do the correction in the trigger which insert the invalid values.

Many Thanks Again.

Regards
Baulraj.V
Previous Topic: Usage of Context variables inside the trigger
Next Topic: A question about model clause (merged by CM)
Goto Forum:
  


Current Time: Tue Dec 06 00:21:15 CST 2016

Total time taken to generate the page: 0.10999 seconds