Comment identifier les valeurs invalides (corrompues) stockées dans les colonnes de DATE D'Oracle
Oracle 10.2.0.5
Quelle est la façon la plus facile d'identifier les lignes dans une table qui ont des valeurs "invalides" dans les colonnes de DATE. Par "invalide" ici ce que je veux dire est une représentation binaire qui viole les règles D'Oracle pour les valeurs de date.
j'ai récemment eu un problème avec une date invalide stockée dans une colonne.
j'ai pu utiliser un prédicat de requête pour trouver une ligne problématique particulière:
WHERE TO_CHAR(date_expr,'YYYYMMDDHH24MISS') = '00000000000000'
dans le cas que j'avais, le CENTURY byte était invalide...
select dump(h.bid_close_date) from mytable h where h.id = 54321
Typ=12 Len=7: 220,111,11,2,1,1,1
dans ce cas, la fonction TO_CHAR renvoie une chaîne de caractères identifiable, que je pourrais utiliser pour identifier la valeur de date wonky.
ma question: Est-ce qu'il y a une approche plus générale ou plus facile (de préférence en utilisant une instruction SQL SELECT) pour identifier les lignes avec des valeurs "invalides" dans les colonnes DATE.
4 réponses
Cela permet d'invalide mois
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;
mise à jour en utilisant la même clause où, j'ai trouvé que le nombre de mois était zéro dans ces cas.
c'est un scénario assez inhabituel (bien que j'ai déjà rencontré quelque chose de similaire auparavant). Le problème le plus courant est de trouver des dates invalides qui sont tenues comme des chaînes dans une colonne date. Vous pouvez adapter la solution pour cela à votre situation, en construisant votre propre date validator.
quelque Chose comme ceci:
create or replace function is_a_date
( p_date in date )
return varchar2
is
d date;
begin
d := to_date(to_char(p_date, 'SYYYYMMDDHH24MISS'), 'SYYYYMMDDHH24MISS') ;
if d != p_date then
return 'not a proper date';
else
return 'good date';
end if;
exception
when others then
return 'not a date';
end;
/
cela convertit une date en chaîne et vice-versa. Il capte les exceptions lancées par date casting. Si le produit final n'est pas le même que la date d'entrée alors probablement quelque chose s'est perdu dans la traduction; pour être honnête je ne suis pas sûr si la date 12011 serait moulé avec succès à une corde, donc c'est une approche de belt'n'braces. C'est un peu délicat d'écrire cet utilitaire sans quelques données de test!
cette requête identifierait toutes les dates non valides:
select h.id, dump(h.bid_close_date)
from mytable h
where h.bid_close_date is not null
and is_a_date(h.bid_close_date) != 'good date';
sans ajouter de fonction, un prédicat simple
TO_CHAR(date_col,'YYYYMMDDHH24MISS') = '000000000000'
semble satisfaisant pour identifier les valeurs corrompues stockées dans une colonne de DATE Oracle. L'ajout d'une fonction semble être inutile. Vérifier les dates corrompues devrait pouvoir être fait dans une déclaration SQL SELECT, et ne pas exiger d'un utilisateur D'avoir le privilège de fonction de création sur la base de données.
j'opterais pour une méthode qui peut être implémentée comme contrainte de contrôle, probablement en vérifiant que la date est dans la gamme des valeurs légales.
cependant, demandez-vous aussi s'il est valide d'avoir une date de 1066-10-14? C'est une valeur juridique, mais vous n'avez probablement pas les factures imprimées sur la journée, par exemple. Donc, vous pourriez vouloir rouler la date invalide check dans un plus grand numéro de ce que vous considérez vraiment être valide dans le contexte de votre application.