Contrainte de vérification sur champ DATE

Version imprimable

13/09/2006, 17h58
Toshtuk

Contrainte de vérification sur champ DATE
[Oracle 9i] NLS_DATE_FORMAT='DD/MM/YYYY'
L'objectif est de contraindre les dates à une période afin d'éviter les valeurs excentriques.
Code:

1 2 alter table MACHIN add constraint CC_DTMACHIN check (DTMACHIN between '01-JAN-1990' and '01-JAN-2100');
La table est modifiée. J'exécute une requète

Code:

select DTMACHIN from MACHIN

J'ai les enregistrements attendus avec des dates correctes.
Maintenant je modifie le format de date.

Code:

alter session set NLS_DATE_FORMAT='DD-MON-YY'

J'exécute la requète et là "fin de fichier de communication" et sur le fichier log "ORA-07445: exception trouvée : vidage coeur ... [Address not mapped to object] ".

Si quelqu'un a une ébauche d'explication, je suis preneur merci d'avance.

PS: j'ai une solution avec un trigger mais cela ne m'interresse pas.
J'ai aussi essayé le code suivant mais cela ne change rien.
Code:

1 2 3 alter table MACHIN add constraint CC_DTMACHIN check (DTMACHIN between to_date('01/01/1990', 'dd/mm/yyyy') and to_date('01/01/2100', 'dd/mm/yyyy'));

Code:

1
2
3
alter table MACHIN add constraint CC_DTMACHIN       
  check (to_date(DTMACHIN,'dd/mm/yyyy')  between to_date('01/01/1990', 'dd/mm/yyyy') 
    and to_date('01/01/2100', 'dd/mm/yyyy'));

14/09/2006, 09h58
Toshtuk
La réponse d'Oraman ne peut fonctionner. Pour s'en convaincre, il suffit d'exécuter la requète suivante
Code:

1 2 select to_char(to_date(DTMACHIN, 'dd/mm/yyyy'), 'dd/mm/yyyy') as DT1, to_char(DTMACHIN, 'dd/mm/yyyy') as DT2 from MACHIN
La date DT1 est fausse, l'erreur provient de la conversion implicite de la fonction to_date.

Toutefois cela m'a donné une idée qui fonctionne mais je ne suis pas convaincu que cela soit la meilleure. Je préférerai ne pas avoir à convertir le champ date.
Code:

1 2 alter table MACHIN add constraint CC_DTMACHIN check (to_char(DTMACHIN, 'yyyymmdd') between '19900101' and '21000101');

oui t'as raison j'ai oublié un to_char avant le to_date

Code:

1
2
3
alter table MACHIN add constraint CC_DTMACHIN       
  check (to_date(to_char(DTMACHIN,'dd/mm/yyyy')),'dd/mm/yyyy')  between to_date('01/01/1990', 'dd/mm/yyyy') 
    and to_date('01/01/2100', 'dd/mm/yyyy'));

là tu compare des dates

14/09/2006, 11h48
McM
si DTEMACHIN est une date, je ne vois pas pourquoi la constraint CHECK ne marche pas.
Code:

1 2 3 4 ALTER TABLE MACHIN ADD CONSTRAINT MACHIN_DTE CHECK (DTE BETWEEN TO_DATE('01.01.2004', 'dd.mm.yyyy') AND TO_DATE('01-01/2100', 'dd-mm/yyyy') );
J'ai essayé, aucun problème.

Citation:

INSERT INTO MACHIN
VALUES(TO_DATE('01-JAN-2000', 'DD-MON-YYYY'))
ORA-02290: check constraint (MCM.MACHIN_DTE) violated
14/09/2006, 15h53
Toshtuk

Pour répondre à McM, DTMACHIN est une date. Le problème survient lorsque je modifie la variable NLS_DATE_FORMAT. Cette modification me permet de tester des configurations différentes.

Pour suivre la réponse de Oraman, la solution fonctionne. Mais 4 appels de fonction dans la contrainte, je trouve que c'est un peu lourd.
14/09/2006, 16h04
Oraman

Citation:

Envoyé par Toshtuk

Mais 4 appels de fonction dans la contrainte, je trouve que c'est un peu lourd.

ça a ebngendré des lenteurs ? bizarre 8O
14/09/2006, 17h03
McM

Citation:

Envoyé par Toshtuk

Pour répondre à McM, DTMACHIN est une date. Le problème survient lorsque je modifie la variable NLS_DATE_FORMAT. Cette modification me permet de tester des configurations différentes.

Pour suivre la réponse de Oraman, la solution fonctionne. Mais 4 appels de fonction dans la contrainte, je trouve que c'est un peu lourd.

J'avais vu mais je ne comprend pas, une date est stockée en base dans un format interne, avec les heures, minutes, quelque soit le NLS_DATE_FORMAT.

J'ai testé en modifiant mon NLS_DATE_FORMAT au niveau session et j'ai eu aucun problème.

Pour info, Quelle version et système utilises tu ?
14/09/2006, 17h31
Yorglaa
peut-être un problème de conversion lié à l'utilisation de l'année YY et pas RR...

exemple :
Code:

1 2 3 select to_date(sysdate,'DD.MM.RRRR') from dual 14.09.2006
et
Code:

1 2 3 select to_date(sysdate,'DD.MM.YYYY') from dual 14.09.0006
14/09/2006, 18h10
Toshtuk
A Yorglaa, C'est la même erreur qu'Oraman. Si tu remplaces sysdate par une date fixe (par exemple '17/09/2006'), tu n'auras plus l'erreur.

Pour McM,
serveur [redhat Oracle9i] NLS_DATE_FORMAT='DD-MON-RRRR'
client [windowsXP Oracle9i] NLS_DATE_FORMAT='DD/MM/YYYY'
Je ne pense pas que cela soit important mais j'ai une séquence sur la date DTMACHIN.

Pour Oraman, j'ai conscience que ce qui suit est étrange et c'est cela aussi que j'essaye de comprendre. La contrainte influence les requêtes 8O.

Sinon je pense que je vais opter pour cette solution.
Code:

1 2 alter table MACHIN add constraint CC_DTMACHIN check (extract(year from DTMACHIN) between 1990 and 2100);
15/09/2006, 08h18
Yorglaa

je ne comprends pas une chose, qu'est-ce que tu entends par "j'ai une séquence sur DTMACHIN ?"

je ne suis pas au courant qu'on puisse utiliser une séquence sur une date...
15/09/2006, 09h18
Toshtuk

Citation:

"j'ai une séquence sur DTMACHIN ?"

Je me suis trompé. Je voulais dire un index.
15/09/2006, 11h47
remi4444

Citation:

Envoyé par Toshtuk

J'exécute la requète et là "fin de fichier de communication" et sur le fichier log "ORA-07445: exception trouvée : vidage coeur ... [Address not mapped to object] ".

C'est surement un bug oracle ça, ou un problème d'install, c'est balot, parceque à mon avis la seule bonne solution est de faire une comparaison de ta date avec des to_date, parceque faire dépendre une contraite d'une variable de session client, faut aimer le risque... :aie:

Citation:

Envoyé par Oraman

to_date(to_char(DTMACHIN,'dd/mm/yyyy')),'dd/mm/yyyy')

8O ça sert à quoi de convertir une date en caractères pour la reconvertir en date ??? 8O