Help - Search - Members - Calendar
Full Version: Grmpf ... SYsdate .... what the hell ???
Oracle DBA Forums > Oracle > Oracle Forum
stadja
Hello everybody smile.gif nice to meet you !

Okay, let me explain.
I'm working under oracle and i don't understand why i have a fault in my table !!!

The problem is in the DateDeNaissance date constraint check_18 check((trunc(Sysdate,'YEAR')-trunc(DateDeNaissance,'YEAR')) >=18), ...

This check is here in order to verify that the Collaborateur is 18 ....
do someone can see where the fu*$%ing fault is ???


create table Collaborateur (
statut char(25) constraint check_statut check(statut='volontaire_benevole' or statut = 'membre_DAssociation'),
Grade char(25) not null,Fonction char(25) not null,
DateDeNaissance date constraint check_18 check((trunc(Sysdate,'YEAR')-trunc(DateDeNaissance,'YEAR')) >=18),
Matricule char(25) not null,
NomNom char(25) not null,
NomPrénom char(25) not null,
N° integer,
primary key (Matricule));


hmmm ... please help smile.gif
stadja
oh yes ... sorry ...
here it is )

Error 2436
ORA-02436: date or system variable wrongly specified in CHECK constraint (DBD ERROR: error possibly near <*> indicator at char 254 in ' create table Collaborateur ( statut char(25) constraint check_statut check(statut='volontaire_benevole' or statut = 'membre_DAssociation'), Grade char(25) not null,Fonction char(25) not null, DateDeNaissance date constraint check_18 check((trunc(<*>Sysdate,'YEAR')-trunc(DateDeNaissance,'YEAR')) >=18), Matricule char(25) not null, NomNom char(25) not null, NomPrénom char(25) not null, N° integer, primary key (Matricule))')
mbobak
From the Concepts manual, at URL http://download-west.oracle.com/docs/cd/B1...integ.htm#11424



The Check Condition

CHECK constraints enable you to enforce very specific integrity rules by specifying a check condition. The condition of a CHECK constraint has some limitations:

* It must be a Boolean expression evaluated using the values in the row being inserted or updated, and
* It cannot contain subqueries; sequences; the SQL functi
* SYSDATE, UID, USER, or USERENV; or the pseudocolumns LEVEL or ROWNUM.



Note the last condition.

-Mark
stadja
ow .... and then how can i do ? sad.gif((
Alien
As far as I know, only one way left . Use a trigger.
After insert/update trigger for each row. Use the same validation against :new.datedenaisance. And raise an error if the collaborateur is over 18.

Regards,

Arian
stadja
pfff ...i'm just so bad !!!
now i've made my beautiful trigger :

CREATE TRIGGER Maj_18
AFTER UPDATE OR INSERT
ON Collaborateur
FOR EACH ROW
BEGIN
IF trunc(Sysdate,'YEAR')-trunc(:new.DateDeNaissance,'YEAR')) < 18 THEN
RAISE_APPLICATION_ERROR(-20005,'Le collaborateur doit avoir 18 ans REVOLUS');
END IF;
END;

but it writes something like :

QUOTE
CREATE TRIGGER Maj_18
AFTER UPDATE OR INSERT
ON Collaborateur
FOR EACH ROW
BEGIN
IF trunc(Sysdate,'YEAR')-trunc(:new.DateDeNaissance,'YEAR')) < 18 THEN
RAISE_APPLICATION_ERROR(-20005,'Le collaborateur doit avoir 18 ans REVOLUS')

Error 24344
ORA-24344: success with compilation error (DBD SUCCESS_WITH_INFO: error possibly near <*> indicator at char 15 in 'CREATE TRIGGER <*>Maj_18 AFTER UPDATE OR INSERT ON Collaborateur FOR EACH ROW BEGIN IF trunc(Sysdate,'YEAR')-trunc(:new.DateDeNaissance,'YEAR')) < 18 THEN RAISE_APPLICATION_ERROR(-20005,'Le collaborateur doit avoir 18 ans REVOLUS')')

END IF
Error 900
ORA-00900: invalid SQL statement (DBD ERROR: error possibly near <*> indicator at char 3 in ' <*>END IF')

END
Error 900
ORA-00900: invalid SQL statement (DBD ERROR: error possibly near <*> indicator at char 2 in ' <*>END')


Strange no ???

and moreover i got a worst problem !!!

when i do :
trunc(Sysdate,'YEAR')-trunc(:new.DateDeNaissance,'YEAR')) < 18
thanks to the oracle treatment of date i can't have the good result because if i write as DateDeNaissance : '01 Mar 1977' it just keeps 77 and not 1977 ... so it leads to a bug ... because ... well you can understand why !


hmmm ....
well sorry i'm a Oracle newbie )
mbobak
You missed a parenthesis. Add one after the 'IF' and before the first "trunc",
and your code should compile. But, yes, you're right, it's flawed.

I would replace:

"IF (trunc(Sysdate,'YEAR')-trunc(:new.DateDeNaissance,'YEAR')) < 18 THEN"

with something like:

"IF (sysdate-:new.DateDeNaissance)/365.2425 < 18 THEN"

if the DateDeNaissance field contains hours, minutes, seconds. If doesn't,
add a trunc() around sysdate, like this:

"IF (trunc(sysdate)-:new.DateDeNaissance)/365.2425 < 18 THEN"

Hope that helps,

-Mark
stadja
Hey thanks for the parenthesis .... but ... it still don't compile .... hrmmmm ...
okay .... i don't wanna bother you .... i'm sorry


CREATE TRIGGER Maj_18
AFTER UPDATE OR INSERT
ON Collaborateur
FOR EACH ROW
BEGIN
IF ((trunc(sysdate)-:new.DateDeNaissance)/365.2425 < 18) THEN
RAISE_APPLICATION_ERROR(-20005,'Le collaborateur doit avoir 18 ans REVOLUS');
END IF;
END;


QUOTE
CREATE TRIGGER Maj_18
AFTER UPDATE OR INSERT
ON Collaborateur
FOR EACH ROW
BEGIN
IF ((trunc(sysdate)-:new.DateDeNaissance)/365.2425 < 18) THEN
RAISE_APPLICATION_ERROR(-20005,'Le collaborateur doit avoir 18 ans REVOLUS')

Error 24344
ORA-24344: success with compilation error (DBD SUCCESS_WITH_INFO: error possibly near <*> indicator at char 17 in ' CREATE TRIGGER <*>Maj_18 AFTER UPDATE OR INSERT ON Collaborateur FOR EACH ROW BEGIN IF ((trunc(sysdate)-:new.DateDeNaissance)/365.2425 < 18) THEN RAISE_APPLICATION_ERROR(-20005,'Le collaborateur doit avoir 18 ans REVOLUS')')


END IF

Error 900
ORA-00900: invalid SQL statement (DBD ERROR: error possibly near <*> indicator at char 3 in ' <*>END IF')


END

Error 900
ORA-00900: invalid SQL statement (DBD ERROR: error possibly near <*> indicator at char 2 in ' <*>END')


moreover i'll add the Collaborateur table :

QUOTE
create table Collaborateur (
statut char(25) constraint check_statut check(statut='volontaire_benevole' or statut = 'membre_DAssociation'),
Grade char(25) not null,
Fonction char(25) not null,
DateDeNaissance date ,
Matricule char(25) not null,
NomNom char(25) not null,
NomPrénom char(25) not null,
N° integer,
primary key (Matricule));


.... pffff smile.gif sorry for that guys
Alien
Just a guess unsure.gif

Create or replace?
stadja
well ... create smile.gif
stadja
OKAY ... it works ... :

QUOTE
CREATE OR REPLACE TRIGGER Maj_18
AFTER UPDATE OR INSERT
ON Collaborateur
FOR EACH ROW
BEGIN
IF ((trunc(sysdate)-:new.DateDeNaissance)/365.2425 < 18) THEN
RAISE_APPLICATION_ERROR(-20005,'Le collaborateur doit avoir 18 ans REVOLUS');
END IF;
END;
/


and guess what ? now i have a new one ... sad.gif
well i've done that :

QUOTE
Create OR REPLACE Trigger Maj_Convoi_Medical_Controles
AFTER UPDATE OR INSERT ON Controles
declare i PLS_INTEGER;
begin
  select count(*) into i from Convoi, ConvoiMedical where (ConvoiMedical.Convoi.Date_JJMMAAAA > :new.Date_JJMMAAAA and Convoi.Code=Controles.Code and ConvoiMedical.Code = Convoi.Code);
  if(i>0)then
  RAISE_APPLICATION_ERROR(-2,'Controle effectué avant la date de la traversée .... annulation de la derniereaction ');
  end if;
end ;
/


and it tells me that :
QUOTE
ERROR at line 1:
ORA-04082: NEW or OLD references not allowed in table level triggers


okay i understand the matter ... but how can i replace :new.Date_JJMMAAAA ???

i don't understand ... in fact i didn't knew that it could have problem because of that ... grmbmlbl
Alien
Hi,

add "for each row", to make it a row-level trigger.

Regards,

Arian
stadja
Okay thanks to all of us i got something like :

QUOTE
Create OR REPLACE Trigger Maj_Convoi_Medical_Controles
AFTER UPDATE OR INSERT ON Controles
FOR EACH ROW
declare i PLS_INTEGER;
begin
select count(*) into i from Convoi c, ConvoiMedical cm where (cm.Convoi.Date_JJMMAAAA > :new.Date_JJMMAAAA and c.Code=Controles.Code and cm.Code = c.Code);
if(i>0)THEN
  RAISE_APPLICATION_ERROR(-2,'Controle effectué avant la date de la traversée .... annulation de la derniereaction ');
end if;
end ;
/


it said me something like ...

QUOTE
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1      PL/SQL: SQL Statement ignored
3/66    Please-00414: no column 'CONVOI' in table


in fact i got a table which name is ConvoiMedical who got a reference to Convoi ...
how can i go throught that reference ???

and how can i create this ConvoiMedical then ???
(i mean how to insert a new one with the Convoi attributes ???)

QUOTE
create table ConvoiMedical (
Code char(25) not null,
primary key (Code));

alter table ConvoiMedical add constraint FKConCon
    foreign key (Code)
    references Convoi;


smile.gif
thanks
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.