-2

I have an issue with an old application. I tried to update date field with the value 01-JAN-99 but my application crash immediately. In fact, I saw a strange date value in the table when I get the value with oracle function dump(): Typ=12 Len=7: 1,1,1,1,1,1,1 and after I get the value with to_char(t$demi, 'SYYYY/MM/DD HH24:MI:SS') => 00000/00/00 00:00:00.

select 
 t$demi
 ,dump (t$demi) 
 ,to_char(t$demi, 'SYYYY/MM/DD HH24:MI:SS')
from 
T;

Result:

01-JAN-99   Typ=12 Len=7: 199,199,1,1,1,1,1  9999/01/01 00:00:00
01-JAN-99   Typ=12 Len=7: 1,1,1,1,1,1,1 00000/00/00 00:00:00

How to insert or update this kind of value in a existing table?

tostao
  • 2,803
  • 4
  • 38
  • 61
zoltix
  • 11
  • 1
  • 2
  • Related: https://stackoverflow.com/questions/12455152/ –  Sep 13 '19 at 06:39
  • Please post the [SQL] `UPDATE` statement that crashed. If you got an error message, then post that also. For your information, Oracle's `DATE` data-type uses 7 (seven) bytes of storage. This old Oracle 9i [documentation](https://docs.oracle.com/cd/B10501_01/appdev.920/a96584/oci03typ.htm#438305) contains an example along with an explanation. – Abra Sep 13 '19 at 07:16

1 Answers1

0

Thanks for this post Trying to export a Oracle via PL/SQL gives a date of 0000-00-00

I converted the dump with dbms_stats.convert_raw_value('01010101010101', d) and I updated the table with the raw value 

create table t42(dt date);

declare
    d date;
begin

    dbms_stats.convert_raw_value('01010101010101', d);
    insert into t42 (dt) values (d);
end;
/
select 
  dt,
  dump(dt), 
  to_char(dt, 'SYYYY/MM/DD HH24:MI:SS')
from t42

select * from t42

resutlts 

9999-01-01 00:00:00 Typ=12 Len=7: 1,1,1,1,1,1,1 00000/00/00 00:00:00

And very strange because the value is less than current date… 9999 is less than 2019!!! 

select 
  dt,
  dump(dt), 
  to_char(dt, 'SYYYY/MM/DD HH24:MI:SS')
from t42
where dt < SYSDATE ;

9999-01-01 00:00:00 Typ=12 Len=7: 1,1,1,1,1,1,1 00000/00/00 00:00:00
zoltix
  • 11
  • 1
  • 2