Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ronnie.

Asked: November 28, 2001 - 10:34 am UTC

Last updated: November 08, 2004 - 4:35 pm UTC

Version: 7.3.4.5

Viewed 1000+ times

You Asked

We have to import data from several hundred V7 databases. The imports complete successfully. But we have discovered records that contain invalid time entries in the date field e.g.

Typ=12 Len=7: 120,100,4,3,100,123,123

When we try to select this date we get the error ora-01801 date format is to long for internal buffer.

What we need to be able to do is select out these bad records and update them with a valid time.

Any ideas on how to do this.

and Tom said...

yuck, that means you have some program out there that is putting dates in in BINARY (the source databases are messed up as well). You need to FIND that application and have them STOP using that approach -- they need to stop using the internal format (which they don't know how to do properly) and use TO_DATE on the way in and TO_CHAR on the way out...

Now, I don't know exactly how to FIX your date. The date component is OK, that is

century = 20 (120-100)
year = 00 (100-100)
month = april (4)
day = 3

thats april 3rd 2000. Now the TIME component is really messed up.

the hours are only allowed to range from 1..24, you have 100
the minutes from 1..60, you have 123
the seconds from 1..60, you have 123

Looks like whatever developer did this to you just put in the DATE part and didn't set the time component properly.

If the time component is not relevent to you -- you can:

update your_table set date_column = trunc(date_column);


I tried this -- it worked in 817 anyway without error and fixed the date. I put the bad date in there via:

static void process()
{

typedef char DATE_TYPE[7];

EXEC SQL TYPE DATE_TYPE is DATE;

typedef struct mystruct
{
DATE_TYPE x;
int y;
}
mystruct_type;

mystruct_type data = { {120,100,4,3,100,123,123}, 1 };

EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL DROP TABLE TEST_DATE_TBL;

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CREATE TABLE TEST_DATE_TBL ( X DATE, Y INT );

EXEC SQL INSERT INTO TEST_DATE_TBL (x,y) VALUES ( :data );

EXEC SQL COMMIT WORK;
}


and after I trunc'ed it -- the date was fine.


If you have some method of telling me what 100, 123, 123 represents in TIME, I can help you write a pro*c app that will work much like the above -- treating the dates as 7 byte arrays of data, we can fix up the times and put them back...




Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Helpful but how do I select out the records with invalid times

Ronnie Doggart, November 29, 2001 - 4:25 am UTC

Tom,

Many thanks, but could you give me an example of how to select the invalid rows out and update them to a fixed time of say 08:00:00

Tom Kyte
November 29, 2001 - 9:06 am UTC 

Here is one method (but you better track down the program that is causing the issue in the first place or the bad dates will just keep coming back!)

ops$ora734@ORA734.WORLD> select x, y, dumpx, to_char(x,'dd-mon-yyyy hh24:mi:ss' ) dt,
  2         substr( dumpx, first_comma+1, second_comma-first_comma-1 ) hour,
  3         substr( dumpx, second_comma+1, third_comma-second_comma-1 ) minute,
  4         substr( dumpx, third_comma+1 ) second
  5    from (select x, y, dump(x) dumpx,
  6                 instr(dump(x),',',1,4) first_comma,
  7                 instr(dump(x),',',1,5) second_comma,
  8                 instr(dump(x),',',1,6) third_comma
  9            from test_date_tbl )
 10  /
ERROR:
ORA-01801: date format is too long for internal buffer



no rows selected

ops$ora734@ORA734.WORLD> select x, y, dumpx,
  2         substr( dumpx, first_comma+1, second_comma-first_comma-1 ) hour,
  3         substr( dumpx, second_comma+1, third_comma-second_comma-1 ) minute,
  4         substr( dumpx, third_comma+1 ) second
  5    from (select x, y, dump(x) dumpx,
  6                 instr(dump(x),',',1,4) first_comma,
  7                 instr(dump(x),',',1,5) second_comma,
  8                 instr(dump(x),',',1,6) third_comma
  9            from test_date_tbl )
 10  /

X                  Y DUMPX                HOUR MINU SECO
--------- ---------- -------------------- ---- ---- ----
03-APR-00          1 Typ=12 Len=7: 120,10 100  123  123
                     0,4,3,100,123,123

29-NOV-01          2 Typ=12 Len=7: 120,10 10   6    5
                     1,11,29,10,6,5

<b>we have one good date, one bad date (y=1 is bad)</b>

ops$ora734@ORA734.WORLD> update
  2         (select x, y, dump(x) dumpx,
  3                 instr(dump(x),',',1,4) first_comma,
  4                 instr(dump(x),',',1,5) second_comma,
  5                 instr(dump(x),',',1,6) third_comma
  6            from test_date_tbl )
  7     set x = trunc(x) + 8/24
  8   where NOT substr( dumpx, first_comma+1, second_comma-first_comma-1 ) between 1 and 24 OR
  9         NOT substr( dumpx, second_comma+1, third_comma-second_comma-1 ) between 1 and 60 OR
 10         NOT substr( dumpx, third_comma+1 ) between 1 and 60
 11  /

1 row updated.

ops$ora734@ORA734.WORLD> select x, y, dumpx, to_char(x,'dd-mon-yyyy hh24:mi:ss' ) dt,
  2         substr( dumpx, first_comma+1, second_comma-first_comma-1 ) hour,
  3         substr( dumpx, second_comma+1, third_comma-second_comma-1 ) minute,
  4         substr( dumpx, third_comma+1 ) second
  5    from (select x, y, dump(x) dumpx,
  6                 instr(dump(x),',',1,4) first_comma,
  7                 instr(dump(x),',',1,5) second_comma,
  8                 instr(dump(x),',',1,6) third_comma
  9            from test_date_tbl )
 10  /

X                  Y DUMPX                DT                   HOUR MINU SECO
--------- ---------- -------------------- -------------------- ---- ---- ----
03-APR-00          1 Typ=12 Len=7: 120,10 03-apr-2000 08:00:00 9    1    1
                     0,4,3,9,1,1

29-NOV-01          2 Typ=12 Len=7: 120,10 29-nov-2001 09:05:04 10   6    5
                     1,11,29,10,6,5


ops$ora734@ORA734.WORLD> rollback;

Rollback complete.

<b>Now they are all "good"</b>

same problem

Dilip, February 14, 2003 - 11:48 am UTC 

Oracle 8.1.7.4
I have a table C.U with public readonly rights (schema C)
When I log in as user D, do select * from C.U ; works fine.
also the sql in this trail select dump(x).... works fine.


I have another instance oracle 9i.
using database link, I select the table U@MY_LINK; gives ORA-01801

I tried using sql with dump, gives another error.
Can you please help?





**************************
oracle 8.1.7.4
**************************
select uid, DT from C.U 

      UID DT
--------- ---------
        1 29-OCT-99
        2 29-OCT-99
       10 29-OCT-99
       11 29-OCT-99
       12 29-OCT-99
       13 29-OCT-99
       33 29-OCT-99
       35 29-OCT-99

8 rows selected.

select dumpx, to_char(DT,'dd-mon-yyyy hh24:mi:ss' ) dt,
substr( dumpx, first_comma+1, second_comma-first_comma-1 ) hour,
substr( dumpx, second_comma+1, third_comma-second_comma-1 ) minute,
substr( dumpx, third_comma+1 ) second
from (select DT,  dump(DT) dumpx,
instr(dump(DT),',',1,4) first_comma,
instr(dump(DT),',',1,5) second_comma,
instr(dump(DT),',',1,6) third_comma
from u@MY_LINK )
/


DT       DUMPX                                                                            DT                   HOUR                                                                             MINUTE                                                                           SECOND                                                                          
-------------------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
29-OCT-99            Typ=12 Len=7: 119,199,10,29,1,1,1                                                29-oct-1999 00:00:00 1                                                                                1                                                                                1                                                                               
29-OCT-99            Typ=12 Len=7: 119,199,10,29,1,1,1                                                29-oct-1999 00:00:00 1                                                                                1                                                                                1                                                                               
29-OCT-99            Typ=12 Len=7: 119,199,10,29,1,1,1                                                29-oct-1999 00:00:00 1                                                                                1                                                                                1                                                                               
29-OCT-99            Typ=12 Len=7: 119,199,10,29,1,1,1                                                29-oct-1999 00:00:00 1                                                                                1                                                                                1                                                                               
29-OCT-99            Typ=12 Len=7: 119,199,10,29,1,1,1                                                29-oct-1999 00:00:00 1                                                                                1                                                                                1                                                                               
29-OCT-99            Typ=12 Len=7: 119,199,10,29,1,1,1                                                29-oct-1999 00:00:00 1                                                                                1                                                                                1                                                                               
29-OCT-99            Typ=12 Len=7: 119,199,10,29,1,1,1                                                29-oct-1999 00:00:00 1                                                                                1                                                                                1                                                                               
29-OCT-99            Typ=12 Len=7: 119,199,10,29,1,1,1                                                29-oct-1999 00:00:00 1                                                                                1                                                                                1                                                                               
8 rows selected.


**************************
oracle 9i
**************************
If I do
select UID, DT from U@MY_LINK ;

gives ERROR:
ORA-03120: two-task conversion routine: integer overflow


If I do 
select * from U@MY_LINK ;
gives
ERROR:
ORA-01801: date format is too long for internal buffer

If I run the sql dump(x)...
ERROR:
ORA-03120: two-task conversion routine: integer overflow

even this sql gives 03120
SQL> select dump(dt) dumpx from u@MY_LINK ;
ERROR:
ORA-03120: two-task conversion routine: integer overflow

Tom Kyte
February 14, 2003 - 8:33 pm UTC

uumm, does ANYTHING about that dblink work? looks like a definite setup/configuration issue here.

ORA-03120: two-task conversion routine: integer overflow

reader, March 24, 2003 - 2:05 am UTC

Tom,

i encountered the following error :

ORA-03120: two-task conversion routine: integer overflow

when accessing a view through dabatase link. Its a simple select statement using a dblink on the view. The following lines are from the error documentation.

Error: ORA 3120
Text: two-task conversion routine: integer overflow
-------------------------------------------------------------------
Cause: An integer value in an internal Oracle structure overflowed
when being sent or received over a heterogeneous connection.
This can happen when an invalid buffer length or too great
a row count is specified.
It usually indicates a bug in the user application.
Action: Check parameters to Oracle calls.
If the problem recurs, reduce all integer parameters,
column values <not> included, to less than 32767.

my question(s) for you are :

1.'heterogeneous connection' - yes i have the dblink to oracle 8i database and i am connecting from oracle 9i. when i use the same select using a java program i am not encountering the error but when i use sqlplus i do get this error. - why is it?.

2.'This can happen when an invalid buffer length or too great a row count is specified' - can you explain this lines to me - in my case the size of the column which i am selecting is varchar2(25) only!

3.'If the problem recurs, reduce all integer parameters,
column values <not> included, to less than 32767.' - reduce the column size or the value for that coulmn? and why is it less than 32767 ?

Thank you ..


Tom Kyte
March 24, 2003 - 7:45 am UTC

contact support for this one. they'll most likely ask you to set some events to diagnose this.

please provide me the interpretation of the error descirption

reader, March 24, 2003 - 8:16 am UTC

tom,

but for the solution can you please provide me the interpretation of the error descirption. i understand that, this is something to do internal to oracle but wanted to understand the error description.

i did not understand 'buffer length' (of what?) and the lines of 3rd question.

thanks



Tom Kyte
March 24, 2003 - 9:14 am UTC

nothing to explain from my end. the fact that it happens in environment 1 and not in 2 indicates "product issue or configuration issue".

It is saying the internal bind buffers are not set up right -- someones messed up something somewhere.

contact support. my interpretation of the error isn't going to help us here.

could you please tell about internal format

vijays, November 08, 2004 - 5:07 am UTC

Hi Tom,
good day to you, as you have said "yuck, that means you have some program out there that is putting dates in in
BINARY (the source databases are messed up as well). You need to FIND that
application and have them STOP using that approach -- they need to stop using
the internal format (which they don't know how to do properly) and use TO_DATE
on the way in and TO_CHAR on the way out..." can you please tell how dates are internally stored in oracle I get some idea in your answer but if you put some more light on this will be really helpful.

thanks and regards as always.
vijays

Tom Kyte
November 08, 2004 - 4:35 pm UTC

why -- I said "don't do it", the only thing you could do with that knowledge is "try to do it"

we used to document it -- but it is so dangerous, don't go there.

you can look in the v7.3 OCI guide for details -- it is out there but not in the recent stuff. every time, without exception, someone uses it -- they mess it up and ora-600 is the result (or "apparently wrong answers" )

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.