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 |
Andrey_R
Messages: 441 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 #672851 is a reply to message #672850] |
Mon, 29 October 2018 09:58 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
BlackSwan wrote on Mon, 29 October 2018 16:50post 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 #672853 is a reply to message #672852] |
Mon, 29 October 2018 10:57 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
BlackSwan wrote on Mon, 29 October 2018 17:36https://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 #672855 is a reply to message #672851] |
Mon, 29 October 2018 11:54 |
|
Michel Cadot
Messages: 68694 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 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
BlackSwan wrote on Mon, 29 October 2018 18:37Let 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 #672859 is a reply to message #672856] |
Mon, 29 October 2018 12:52 |
|
Michel Cadot
Messages: 68694 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 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 29 October 2018 19:52Michel 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 #672870 is a reply to message #672861] |
Tue, 30 October 2018 04:16 |
Andrey_R
Messages: 441 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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 18:36:17 CDT 2024
|