How to identify invalid (corrupted) values stored in Oracle DATE columns
This identifies invalid months
SELECT rowid,
pk_column,
DUMP(date_column, 1010) AS dump1
FROM table
WHERE TO_NUMBER(SUBSTR(DUMP(date_column, 1010), INSTR(DUMP( date_column, 1010),
',', 1, 2
) + 1,
INSTR(DUMP(date_column, 1010), ',', 1, 3) - (
INSTR(DUMP( date_column, 1010), ',', 1, 2) + 1
))) = 0;
Update using the same where clause, I found the month number was zero in these cases.
Why am I seeing values of '2432-82-75 50:08:01' in Oracle DATE column?
Unfortunately, Oracle database engine allows inserting invalid date values, which leads to many problems especially when importing data to others database engines such as SQL Server.
To handle this issue, you have to implement the logic that fits your needs, as example:
- You can exclude these records from you queries by filtering on acceptable date ranges: (WHERE date between ...)
- You can Update records with invalid values by replacing with
NULL - You can use a
CASEstatement in your query to replace values withNULL
I faced this issue one time while importing data to SQL Server from an Oracle data source, there was unacceptable date values, i decided to update all records where date are invalid and replace with NULL values before starting the import process.
There are many links related to this issue:
- Detecting invalid values in the DB
- How to identify invalid (corrupted) values stored in Oracle DATE columns
- Corrupt date fields causing query failure in Oracle
- Invalid Date in DATE Column SQLPlus VS SQLDeveloper
- Ask Tom - date validation in oracle
- Dealing with invalid dates
- Error: Invalid date format
- DB Connect; Oracle DB date field data is corrupt
select invalid date string in oracle
you can write a function that try to convert the string into the date:
create function check_date(p_date_str in nvarchar2) return number as
v_result NUMBER(1,0);
v_date DATE;
begin
v_result := 1;
begin
v_date := to_date( p_date_str, 'YYYY-MM-DD');
exception when others then
v_result := 0;
end;
return v_result;
end;
/
and then use it to find invalid records:
select * from my_table where check_date(column_with_date_as_str) = 0;
Oracle date corruption during update
UPDATE:
I don't find any published reference to this specific type of DATE corruption on the Oracle support site. (It may be there, my quick searches just didn't turn it up.)
- Baddate Script To Check Database For Corrupt dates [ID 95402.1]
- Bug 2790435 - Serial INSERT with parallel SELECT and type conversion can insert corrupt data [ID 2790435.8]
The output from the DUMP() function is showing the date value is indeed invalid:
Typ=12 Len=7: 120,110,11,18,13,0,16
We expect that the minutes byte should be a value between one and sixty, not zero.
The 7 bytes of a DATE value represent, in order, century(+100), year(+100), month, day, hour(+1), minutes(+1), seconds(+1).
The only time I have seen invalid DATE values like this when a DATE value was being supplied as a bind variable, from a Pro*C program (where the bind value is supplied in the internal 7 byte representation, entirely bypassing the normal validation routines that catch invalid dates e.g. Feb 30)
There is no reason to expect the behavior you're seeing, given the Oracle syntax you posted.
This is either a spurious anomaly (memory corruption?) or if this is repeatable, then it's a flaw (bug) in the Oracle code. If it's a flaw in the Oracle code, the most likely suspects would be "newish" features in an un-patched release.
(I know CAST is a standard SQL function that's been around for ages in other databases. I guess I'm old school, and have never introduced it into my Oracle-syntax repertoire. I don't know what version of Oracle it was that introduced the CAST, but I would have stayed away from it in the first release it appeared in.)
The big 'red flag' (that another commenter noted) is that CAST( datecol AS DATE).
You would expect the optimizer to treat that as equivalent to date_col ... but past experience shows us that TO_NUMBER( number_col ) is actually interpreted by the optimizer as TO_NUMBER( TO_CHAR ( number_col ) ).
I suspect something similar might be going on with that unneeded CAST.
Based on that one record you showed, I suspect the issue is with values with a "59" value for minutes or seconds, and possibly a "23" value for hours, would be the ones that show the error.
I would try checking for places where the minutes, hour or seconds are stored as 0:
SELECT id, DUMP(activitydate)
FROM newtable
WHERE DUMP(activitydate) LIKE '%,0,%'
OR DUMP(activitydate) LIKE '%,0'
How to fetch the list of errors for invalid objects in Oracle 10g
You could query [DBA/ALL/USER]_ERRORS. It describes current errors on all stored objects (views, procedures, functions, packages, and package bodies) owned by the current user.
Chose which view to query, depending on the privileges you have:
- DBA_ : All objects in the database
- ALL_ : All objects owned by the user and on which the user has been granted privileges
- USER_ : All objects owned by the user
For example,
I create a procedure with a compilation error, and I want to query the error details:
SQL> CREATE OR REPLACE PROCEDURE p
2 BEGIN
3 NULL
4 END;
5 /
Warning: Procedure created with compilation errors.
SQL>
SQL> SELECT NAME, TYPE, line, text FROM user_errors;
NAME TYPE LINE TEXT
----- ---------- ---------- --------------------------------------------------
P PROCEDURE 2 PLS-00103: Encountered the symbol "BEGIN" when exp
ecting one of the following:
( ; is with authid as cluster compress order us
ing compiled
wrapped external deterministic parallel_enable
pipelined
result_cache accessible
SQL>
Read more about it in documentation here
How to prevent bad dates in Oracle?
You can use a function to check whether dates are valid by converting them to a string and back and add this to a CHECK constraint to your date columns to ensure all dates are valid.
However, you should not need to do this as you should be mandating that arbitrary code is not executed on your systems and all the application code should include input validation so that only sanitized values are inserted into the database.
An example:
Oracle Setup:
CREATE TABLE Dates (
d DATE
);
CREATE FUNCTION isValidDate( dt IN DATE ) RETURN NUMBER
IS
d DATE;
BEGIN
d := TO_DATE( TO_CHAR( dt, 'fxYYYY-MM-DD HH24:MI:SS' ), 'fxYYYY-MM-DD HH24:MI:SS' );
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
Insert Dates:
declare
d DATE;
FUNCTION createUnvalidatedDate(
centuries INT := 0,
years INT := 0,
months INT := 0,
days INT := 0,
hours INT := 0,
minutes INT := 0,
seconds INT := 0
) RETURN DATE
IS
dt DATE;
hex_string CHAR(14);
BEGIN
hex_string := LPAD( TO_CHAR( centuries + 100, 'fmXX' ), 2, '0' )
|| LPAD( TO_CHAR( years + 100, 'fmXX' ), 2, '0' )
|| LPAD( TO_CHAR( months, 'fmXX' ), 2, '0' )
|| LPAD( TO_CHAR( days, 'fmXX' ), 2, '0' )
|| LPAD( TO_CHAR( hours + 1, 'fmXX' ), 2, '0' )
|| LPAD( TO_CHAR( minutes + 1, 'fmXX' ), 2, '0' )
|| LPAD( TO_CHAR( seconds + 1, 'fmXX' ), 2, '0' );
dbms_stats.convert_raw_value(hextoraw(hex_string), dt);
RETURN dt;
END;
begin
d := createUnvalidatedDate( 19, 0, 5, 127, 126, 118, 87 );
INSERT INTO Dates ( d ) VALUES ( d );
d := createUnvalidatedDate( 0, 0, 0, 0, 0, 0, 0 );
INSERT INTO Dates ( d ) VALUES ( d );
d := createUnvalidatedDate( 20, 19, 6, 24, 10, 28, 30 );
INSERT INTO Dates ( d ) VALUES ( d );
end;
/
Check Validity:
SELECT d, isValidDate( d ) AS valid
FROM Dates;
Outputs:
D | VALID
:-------------------- | ----:
1900-5-127T126:118:87 | 0
0-0-0T0:0:0 | 0
2019-6-24T10:28:30 | 1
Which shows that the first two values are invalid and the last one is a valid date.
db<>fiddle here
Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
The value stored in that column is not a valid date. The first byte of the dump should be the century, which according to Oracle support note 69028.1 is stored in 'excess-100' notation, which means it should have a value of 100 + the actual century; so 1900 would be 119, 2000 would be 120, and 5500 would be 155. So 44 would represent -5600; the date you have stored appears to actually represent 5544-09-14 BC. As Oracle only supports dates with years between -4713 and +9999, this isn't recognised.
You can recreate this fairly easily; the trickiest bit is getting the invalid date into the database in the first place:
create table t42(dt date);
Table created.
declare
d date;
begin
dbms_stats.convert_raw_value('2c9c090e010101', d);
insert into t42 (dt) values (d);
end;
/
PL/SQL procedure successfully completed.
select dump(dt), dump(dt, 1016) from t42;
DUMP(DT)
--------------------------------------------------------------------------------
DUMP(DT,1016)
--------------------------------------------------------------------------------
Typ=12 Len=7: 45,56,9,14,1,1,1
Typ=12 Len=7: 2d,38,9,e,1,1,1
So this has a single row with the same data you do. Using alter session I can see what looks like a valid date:
alter session set nls_date_format = 'DD-Mon-YYYY';
select dt from t42;
DT
-----------
14-Sep-5544
alter session set nls_date_format = 'YYYYMMDDHH24MISS';
select dt from t42;
DT
--------------
55440914000000
But if I use an explicit date mask it just gets zeros:
select to_char(dt, 'DD-Mon-YYYY'), to_char(dt, 'YYYYMMDDHH24MISS') from t42;
TO_CHAR(DT,'DD-MON-Y TO_CHAR(DT,'YY
-------------------- --------------
00-000-0000 00000000000000
And if I run your procedure:
exec dump_table_to_csv('T42');
The resultant CSV has:
"DT"
"0000-00-00T00:00:00"
I think the difference is that those that attempt to show the date are sticking with internal date data type 12, while those that show zeros are using external data type 13, as mentioned in note 69028.1.
So in short, your procedure isn't doing anything wrong, the date it's trying to export is invalid internally. Unless you know what date it was supposed to be, which seems unlikely given your starting point, I don't think there's much you can do about it other than guess or ignore it. Unless, perhaps, you know how the data was inserted and can work out how it got corrupted.
I think it's more likely to be from an OCI program than what I did here; this 'raw' trick was originally from here. You might also want to look at note 331831.1. And this previous question is somewhat related.
Oracle 10g accepting 5 digit year in a Date
Oracle stores DATEs in tables using 7 bytes where the first 2 bytes are:
- Century + 100
- Year of century + 100
So the maximum date that can (technically) be stored is when those two bytes have the values 255 and 199 which would give the a year of 15599 (I'm ignoring that you could theoretically store 255 in the second byte as that opens up a whole heap of separate issues).
You can convert a raw value to a date using the DBMS_STATS.CONVERT_RAW_VALUE which means we can bypass the normal methods of creating dates and directly generate the byte values which will be stored.
This function is an example of that:
CREATE FUNCTION createDate(
year int,
month int,
day int,
hour int,
minute int,
second int
) RETURN DATE DETERMINISTIC
IS
hex CHAR(14);
d DATE;
BEGIN
hex := TO_CHAR( FLOOR( year / 100 ) + 100, 'fm0X' )
|| TO_CHAR( MOD( year, 100 ) + 100, 'fm0X' )
|| TO_CHAR( month, 'fm0X' )
|| TO_CHAR( day, 'fm0X' )
|| TO_CHAR( hour + 1, 'fm0X' )
|| TO_CHAR( minute + 1, 'fm0X' )
|| TO_CHAR( second + 1, 'fm0X' );
DBMS_OUTPUT.PUT_LINE( hex );
DBMS_STATS.CONVERT_RAW_VALUE( HEXTORAW( hex ), d );
RETURN d;
END;
/
Then if you have a date column you can insert values you are not normally allowed to insert:
CREATE TABLE table_name ( date_column DATE );
INSERT INTO table_name ( date_column )
VALUES ( DATE '2019-12-31' + INTERVAL '1:02:03' HOUR TO SECOND );
INSERT INTO table_name ( date_column ) VALUES ( createDate( 15599, 12, 31, 1, 2, 3 ) );
INSERT INTO table_name ( date_column ) VALUES ( createDate( 12017, 2, 21, 0, 0, 0 ) );
TO_CHAR does not work when the year exceeds the normal bounds of a date. To get the values stored in the table you can use DUMP to get a string containing the byte values or you can use EXTRACT to get the individual components.
SELECT DUMP( date_column ),
TO_CHAR( date_column, 'YYYY-MM-DD' ) AS value,
TO_CHAR( EXTRACT( YEAR FROM date_column ), 'fm00000' )
|| '-' || TO_CHAR( EXTRACT( MONTH FROM date_column ), 'fm00' )
|| '-' || TO_CHAR( EXTRACT( DAY FROM date_column ), 'fm00' )
|| ' ' || TO_CHAR( EXTRACT( HOUR FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' )
|| ':' || TO_CHAR( EXTRACT( MINUTE FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' )
|| ':' || TO_CHAR( EXTRACT( SECOND FROM CAST( date_column AS TIMESTAMP ) ), 'fm00' )
AS full_value
FROM table_name;
outputs:
DUMP(DATE_COLUMN) | VALUE | FULL_VALUE
:-------------------------------- | :--------- | :-------------------
Typ=12 Len=7: 120,119,12,31,2,3,4 | 2019-12-31 | 02019-12-31 01:02:03
Typ=12 Len=7: 255,199,12,31,2,3,4 | 0000-00-00 | 15599-12-31 01:02:03
Typ=12 Len=7: 220,117,2,21,1,1,1 | 0000-00-00 | 12017-02-21 00:00:00
db<>fiddle here
Related Topics
Access SQL Using Top 5 Returning More Than 5 Results
Counting Number of Records Hour by Hour Between Two Dates in Oracle
Calling Stored Procedure from Solr
SQL - Give Me 3 Hits for Each Type Only
Select Distinct Values from 1 Column
How to Script Out Stored Procedures to Files
Set Time Portion of a Datetime Variable
Way to Insert Text Having ' (Apostrophe) into a SQL Table
SQL Server:Does Newid() Always Gives a Unique Id
Rails Virtual Attribute Search or SQL Combined Column Search
How to Input a Nodejs Variable into an SQL Query
Looking for a SQL Transaction Log File Viewer
How to Produce an CSV Output File from Stored Procedure in SQL Server