Home » SQL & PL/SQL » SQL & PL/SQL » date column displays strange output (Oracle 11g RAC, Linux OEL 6)
date column displays strange output [message #672849] Mon, 29 October 2018 09:38 Go to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Hi all,
I have a date column in a table that displays very strange output:




SQL> select distinct to_char(g.DATECOL1, 'dd-mon-yyyy')
  2    from MYTAB g
  3   where g.DATECOL1 < to_date('01/01/2000', 'dd/mm/yyyy');

TO_CHAR(G.DATECOL1
--------------------
16-tue-4028
16-tue-4025
16-tue-4064
16-tue-3995
16-tue-4053
16-tue-4042
16-tue-4036
16-tue-3962

8 rows selected.

SQL> desc MYTAB
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                     NOT NULL NUMBER(14)
 COL2                     NOT NULL NUMBER(14)
 COL3                     NOT NULL NUMBER(14)
 COL4                     NOT NULL VARCHAR2(100 CHAR)
 DATECOL1                 NOT NULL DATE
 COL5                              DATE

SQL>


This looks like this in more than one database, which can be shut down and started up , which means Oracle doesn't recognize any corruption in these phases,
nor do I see any evidence to it...

How can this be explained ?
Re: date column displays strange output [message #672850 is a reply to message #672849] Mon, 29 October 2018 09:50 Go to previous messageGo to next message
BlackSwan
Messages: 26483
Registered: January 2009
Location: SoCal
Senior Member
post results from SQL below

select distinct dump(g.DATECOL1)
from MYTAB g
where g.DATECOL1 < to_date('01/01/2000', 'dd/mm/yyyy');
Re: date column displays strange output [message #672851 is a reply to message #672850] Mon, 29 October 2018 09:58 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

BlackSwan wrote on Mon, 29 October 2018 16:50
post results from SQL below

select distinct dump(g.DATECOL1)
from MYTAB g
where g.DATECOL1 < to_date('01/01/2000', 'dd/mm/yyyy');


SQL>
SQL> select distinct dump(g.DATECOL1)
  2  from MYTAB g
  3  where g.DATECOL1 < to_date('01/01/2000', 'dd/mm/yyyy');

DUMP(G.DATECOL1)
--------------------------------------------------------------------------------
Typ=12 Len=7: 59,158,240,240,1,1,1
Typ=12 Len=7: 59,164,240,240,1,1,1
Typ=12 Len=7: 59,175,240,240,1,1,1
Typ=12 Len=7: 59,136,240,240,1,1,1
Typ=12 Len=7: 59,147,240,240,1,1,1
Typ=12 Len=7: 59,172,240,240,1,1,1
Typ=12 Len=7: 59,205,240,240,1,1,1
Typ=12 Len=7: 59,238,240,240,1,1,1

8 rows selected.
Re: date column displays strange output [message #672852 is a reply to message #672851] Mon, 29 October 2018 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 26483
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/message/14842494#14842494

what exactly populates DATECOL1?
Re: date column displays strange output [message #672853 is a reply to message #672852] Mon, 29 October 2018 10:57 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

BlackSwan wrote on Mon, 29 October 2018 17:36
https://community.oracle.com/message/14842494#14842494

what exactly populates DATECOL1?

SQL> CREATE OR REPLACE FUNCTION  bad_date (dt  IN  DATE)
  2  RETURN  NUMBER  -- 1 for bad DATE, 0 for good DATE or NULL
  3  IS
  4      fmt         VARCHAR2 (21) := 'DD/MM/YYYY HH24:MI:SS';
  5      return_val  NUMBER := 0;
  6  BEGIN
  7      IF  dt  IS NOT NULL
  8      THEN
  9          BEGIN
 10              IF  dt <> TO_DATE ( TO_CHAR (dt, fmt)
 11                                , fmt
 12                                )
 13              THEN
 14                  return_val := 1;
 15               END IF;
 16          EXCEPTION
 17              WHEN  OTHERS
 18                  THEN
 19                      IF  SQLCODE BETWEEN -1876 AND -1841  -- DATE conversion errors
 20                      THEN
 21                          return_val := 1;
 22                      ELSE
 23                          RAISE;
 24                      END IF;
 25          END;
 26
 27      END IF;
 28
 29
 30
 31      RETURN  return_val;
 32  END  bad_date;
 33  /

Function created.

SQL>
SQL> select distinct bad_date(dt => g.DATECOL1 ) from MYTAB g
  2   where g.DATECOL1 < to_date('01/01/2000', 'dd/mm/yyyy');

BAD_DATE(DT=>G.DATECOL1)
--------------------------------
                               1




Or to be exact


			   
							   
SQL>  CREATE OR REPLACE FUNCTION  bad_date (dt  IN  DATE)
  2   RETURN  NUMBER  -- 1 for bad DATE, 0 for good DATE or NULL
  3   IS
  4       fmt         VARCHAR2 (21) := 'DD/MM/YYYY HH24:MI:SS';
  5       return_val  NUMBER := 0;
  6   BEGIN
  7       IF  dt  IS NOT NULL
  8       THEN
  9           BEGIN
 10               IF  dt <> TO_DATE ( TO_CHAR (dt, fmt)
 11                                 , fmt
 12                                 )
 13               THEN
 14                   return_val := 1;
 15                END IF;
 16           EXCEPTION
 17               WHEN  OTHERS
 18                   THEN
 19                       IF  SQLCODE BETWEEN -1876 AND -1841  -- DATE conversion errors
 20                       THEN
 21                            RAISE;/*return_val := 1;*/
 22                       ELSE
 23                           RAISE;
 24                       END IF;
 25           END;
 26
 27       END IF;
 28
 29
 30
 31       RETURN  return_val;
 32   END  bad_date;
 33   /

Function created.

SQL> select distinct bad_date(dt => g.DATECOL1 ) from MYTAB g
  2   where g.DATECOL1 < to_date('01/01/2000', 'dd/mm/yyyy');
select distinct bad_date(dt => g.DATECOL1 ) from MYTAB g
                *
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at "MYUSER.BAD_DATE", line 21


SQL>

Re: date column displays strange output [message #672854 is a reply to message #672853] Mon, 29 October 2018 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 26483
Registered: January 2009
Location: SoCal
Senior Member
Let us stipulate the data within DATECOL1 is corrupt.
So what exactly do you expect & desire from here?
Re: date column displays strange output [message #672855 is a reply to message #672851] Mon, 29 October 2018 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66326
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Wild guess: the date was inserted into the table via a language that can send to Oracle data with DATE datatype, for instance DATE datatype in Pro*C and Pro*Cobol is 7 bytes with the 3 last bytes defaulted to 1 which stands for time 00:00:00.
Now if you use such datatype Oracle expects you give it data in its internal DATE format.
As this has not be done, you have an invalid date but, as I already showed it many years ago in AskTom, TO_CHAR makes the assumption that inside the database the date, if year is between the range [-4712,9999], is valid and so blindly converts it which can lead to weird result or exception.

Now what was the binary value that has been given: 59,158,240,240 (I ignore the last 3 bytes, "1,1,1", already explained and more likely not given by the program), in hexadecimal: 3B9EF0F0, in decimal: 1000272112.
My best guess, this a Unix time number, converted to date/time this gives (assuming time zone UTC):
SQL> select orafaq_pkg.unix_to_oracle(1000272112) from dual;
ORAFAQ_PKG.UNIX_TO_
-------------------
12/09/2001 05:21:52

[Updated on: Mon, 29 October 2018 12:00]

Report message to a moderator

Re: date column displays strange output [message #672856 is a reply to message #672854] Mon, 29 October 2018 11:55 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

BlackSwan wrote on Mon, 29 October 2018 18:37
Let us stipulate the data within DATECOL1 is corrupt.
So what exactly do you expect & desire from here?
Well, I just wanted another opinion, I guess, to make sure I have the correct feeling about this.

From here I guess the direction is to data fix the corruption to a legitimate value and move on with life..
Thanks !

Re: date column displays strange output [message #672857 is a reply to message #672856] Mon, 29 October 2018 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 26483
Registered: January 2009
Location: SoCal
Senior Member
Andrey_R wrote on Mon, 29 October 2018 09:55
BlackSwan wrote on Mon, 29 October 2018 18:37
Let us stipulate the data within DATECOL1 is corrupt.
So what exactly do you expect & desire from here?
Well, I just wanted another opinion, I guess, to make sure I have the correct feeling about this.

From here I guess the direction is to data fix the corruption to a legitimate value and move on with life..
Thanks !

IMO, the highest priority task is to determine how the data got corrupt & to make sure it does not happen again in the future.

Otherwise you will be stuck in another Groundhog Day

1) Fix corrupt data
2) Find new corrupt data
3) GOTO #1
Re: date column displays strange output [message #672859 is a reply to message #672856] Mon, 29 October 2018 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 66326
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 29 October 2018 17:54

Wild guess: the date was inserted into the table via a language that can send to Oracle data with DATE datatype, for instance DATE datatype in Pro*C and Pro*Cobol is 7 bytes with the 3 last bytes defaulted to 1 which stands for time 00:00:00.
Now if you use such datatype Oracle expects you give it data in its internal DATE format.
As this has not be done, you have an invalid date but, as I already showed it many years ago in AskTom, TO_CHAR makes the assumption that inside the database the date, if year is between the range [-4712,9999], is valid and so blindly converts it which can lead to weird result or exception.

Now what was the binary value that has been given: 59,158,240,240 (I ignore the last 3 bytes, "1,1,1", already explained and more likely not given by the program), in hexadecimal: 3B9EF0F0, in decimal: 1000272112.
My best guess, this a Unix time number, converted to date/time this gives (assuming time zone UTC):
SQL> select orafaq_pkg.unix_to_oracle(1000272112) from dual;
ORAFAQ_PKG.UNIX_TO_
-------------------
12/09/2001 05:21:52

Re: date column displays strange output [message #672860 is a reply to message #672859] Mon, 29 October 2018 13:05 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Mon, 29 October 2018 19:52
Michel Cadot wrote on Mon, 29 October 2018 17:54

Wild guess: the date was inserted into the table via a language that can send to Oracle data with DATE datatype, for instance DATE datatype in Pro*C and Pro*Cobol is 7 bytes with the 3 last bytes defaulted to 1 which stands for time 00:00:00.
Now if you use such datatype Oracle expects you give it data in its internal DATE format.
As this has not be done, you have an invalid date but, as I already showed it many years ago in AskTom, TO_CHAR makes the assumption that inside the database the date, if year is between the range [-4712,9999], is valid and so blindly converts it which can lead to weird result or exception.

Now what was the binary value that has been given: 59,158,240,240 (I ignore the last 3 bytes, "1,1,1", already explained and more likely not given by the program), in hexadecimal: 3B9EF0F0, in decimal: 1000272112.
My best guess, this a Unix time number, converted to date/time this gives (assuming time zone UTC):
SQL> select orafaq_pkg.unix_to_oracle(1000272112) from dual;
ORAFAQ_PKG.UNIX_TO_
-------------------
12/09/2001 05:21:52


Can you elaborate about the unix date, and in particular, how did you get from 1000272112 to the date you posted ?
Or at least refer me to a source of info where it's explained ?

TIA
Re: date column displays strange output [message #672861 is a reply to message #672860] Mon, 29 October 2018 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 66326
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unix time or Epoch time is defined as the number of seconds since 01/01/1970 00:00:00.

I used this function:
  FUNCTION unix_to_oracle (in_number IN NUMBER, in_zone IN NUMBER DEFAULT 0)
  RETURN DATE
  IS
  BEGIN
    RETURN TO_DATE('19700101','yyyymmdd') + in_number/86400 + in_zone / 24;
  END;

Re: date column displays strange output [message #672870 is a reply to message #672861] Tue, 30 October 2018 04:16 Go to previous message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Mon, 29 October 2018 20:16

Unix time or Epoch time is defined as the number of seconds since 01/01/1970 00:00:00.

I used this function:
  FUNCTION unix_to_oracle (in_number IN NUMBER, in_zone IN NUMBER DEFAULT 0)
  RETURN DATE
  IS
  BEGIN
    RETURN TO_DATE('19700101','yyyymmdd') + in_number/86400 + in_zone / 24;
  END;

Very interesting.
Thank you.

Investigation continues... *Pink Panther music playing*

[Updated on: Tue, 30 October 2018 04:16]

Report message to a moderator

Previous Topic: Who deleted the data
Next Topic: How to allow multi-users in Procedure
Goto Forum:
  


Current Time: Mon Apr 22 11:33:02 CDT 2019