create trigger, error : PLS-00302: component 'DATETIME' must be declared ! pls help

I'm newbie to Oracle, and don't know much 'bout the statement in Oracle. I'm trying to write a trigger, to check if the new SCREENING_TIME inserted is valid with the others. Here is my trigger :

create or replace trigger CHECK_CONFLICT_SCR
before insert on SCREENING_TIME
for each row
DECLARE
fromD Date;
toD Date;
duree number;
t Timestamp;
h number;
m number;

BEGIN
select BeginValidity,  EndValidity into fromD,toD
from  CINEMA_FILM
where CINEMA_FILM.FilmID = :new.FilmID;

select Duration, CAST (DateTime as Timestamp) into duree,t  
from FILM, OCCURENCE O
where FILM.FilmID = :new.FilmID and O.OccurenceID= :new.OccurenceID;

declare cursor temp
is
select CAST (V.DateTime as Timestamp), F.Duration from VIEW_FILM_SCHEDULE V, FILM F where F.FilmID = V.FilmID and V.RoomID = :new.RoomID and V.DayOfWeek = :new.DayOfWeek and ((V.BeginValidity - fromD)>=0 or (V.EndValidity-toD)<=0);
begin
FOR scr in temp
LOOP
m:=CAST((substr((t-scr.DateTime),instr((t-scr.DateTime),' ')+4,2)) as number);
h:= CAST(substr((t-scr.DateTime),instr((t-scr.DateTime),' ')+1,2) as number);
if ( h < 0 ) then
begin
   if (-h*60-m< duree) then
         raise_application_error(-20240,'Conflict screening time');
  end if;         
end;
elsif ((h*60+m)<scr.Duration)then
       raise_application_error(-20240,'Conflict screening time');
end if;       
END LOOP;
END;
END;

After trying modify many things in wrong statement (i really not familiar with this! :( ), i get the error : "Error Text = PLS-00302: component 'DATETIME' must be declared " at  lines "h := ..." and "m:=..."
Really need your helps! It's a little bit urgent. Thanks in advance!
thanhthanhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>>select CAST (V.DateTime as Timestamp)
this removes the column name...
The below should fix this problem
select CAST (V.DateTime as Timestamp) DateTime

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MetanilCommented:
Check this out:

create or replace trigger CHECK_CONFLICT_SCR
before insert on SCREENING_TIME
for each row
DECLARE
fromD Date;
toD Date;
duree number;
t Timestamp;
h number;
m number;

BEGIN
select BeginValidity,  EndValidity into fromD,toD
from  CINEMA_FILM
where CINEMA_FILM.FilmID = :new.FilmID;

select Duration, CAST (DateTime as Timestamp) into duree,t  
from FILM, OCCURENCE O
where FILM.FilmID = :new.FilmID and O.OccurenceID= :new.OccurenceID;

declare cursor temp
is
select CAST (V.DateTime as Timestamp) DateTime, F.Duration Duration from VIEW_FILM_SCHEDULE V, FILM F where F.FilmID = V.FilmID and V.RoomID = :new.RoomID and V.DayOfWeek = :new.DayOfWeek and ((V.BeginValidity - fromD)>=0 or (V.EndValidity-toD)<=0);
begin
FOR scr in temp
LOOP
m:=CAST((substr((t-scr.DateTime),instr((t-scr.DateTime),' ')+4,2)) as number);
h:= CAST(substr((t-scr.DateTime),instr((t-scr.DateTime),' ')+1,2) as number);
if ( h < 0 ) then
begin
   if (-h*60-m< duree) then
        raise_application_error(-20240,'Conflict screening time');
  end if;        
end;
elsif ((h*60+m)<scr.Duration)then
      raise_application_error(-20240,'Conflict screening time');
end if;      
END LOOP;
END;
END;

Metanil
Guy Hengel [angelIII / a3]Billing EngineerCommented:
seems to be the same correction as my suggestion? only without any explanation :-)
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

thanhthanhAuthor Commented:
hi, angelIII, i do as u suggest and the trigger created successfully.
But i try to insert to SCREENING_TIME and encounter another error  :

ORA-06512: at "QA.C_CONFLICT_SCR", line 10
ORA-04088: error during execution of trigger 'QA.C_CONFLICT_SCR'

U have any suggest? Thanks!
MetanilCommented:
QA.C_CONFLICT_SCR or QA.CHECK_CONFLICT_SCR ??

Please give some detail how you execute (values while inserting), table structure of SCREENING_TIME etc..

Metanil
MetanilCommented:
check dba_errors if there is some compilation errors.

select * from dba_erros where owner='QA' and type='TRIGGER'

Metanil
thanhthanhAuthor Commented:
These are tables and view i use in the trigger :

create table FILM  (
   FilmID               INTEGER                              not null,
   Cast                 VARCHAR(100),
   Genre                VARCHAR(50),
   Duration             INTEGER,
   Distributor          VARCHAR(20),
   Country              VARCHAR(20),
   Synopsis             VARCHAR2(500),
   ParticipantID        INTEGER,
   BeginValidity        DATE,
   EndValidity          DATE,
   Director            VARCHAR2(30),
   Language            VARCHAR2(30),
   Music            VARCHAR2(30),
   ReleaseDay            DATE,
   constraint PK_FILM primary key (FilmID),
 
);

create table CINEMA_FILM  (
   CinemaID             INTEGER                              not null,
   FilmID               INTEGER                              not null,  
   BeginValidity        DATE                             not null,
   EndValidity          DATE,
   constraint PK_CINEMA_FILM primary key (CinemaID, FilmID),
   constraint FK_CINEMA_F_CINEMAFIL_CINEMA foreign key (CinemaID)
         references CINEMA (CinemaID),
   constraint FK_CINEMA_F_CINEMAFIL_FILM foreign key (FilmID)
         references FILM (FilmID)
);
create table OCCURENCE  (
   OccurenceID          INTEGER                              not null,
   EventID              INTEGER,
   ParticipantID        INTEGER,
   PlaceID              INTEGER,
   Datetime             DATE,
   constraint PK_OCCURENCE primary key (OccurenceID),
   constraint FK_OCCURENC_OCCURENCE_EVENT foreign key (EventID)
         references EVENT (EventID),
   constraint FK_OCCURENC_OCCURENCE_PLACE foreign key (PlaceID)
         references PLACE (PlaceID),
   constraint FK_OCCURENC_OCCURENCE_PARTICIP foreign key (ParticipantID)
         references PARTICIPANT (ParticipantID)
);

create table SCREENING_TIME  (
   ScreeningTimeID      INTEGER                              not null,
   RoomID               INTEGER,
   CinemaID             INTEGER,
   FilmID               INTEGER,
   SpecialDate          DATE,
   OccurenceID          INTEGER,
   DayOfWeek            SMALLINT,
   constraint PK_SCREENING_TIME primary key (ScreeningTimeID),
   constraint FK_SCREENIN_SCREENING_ROOM foreign key (RoomID)
         references ROOM (RoomID),
   constraint FK_SCREENIN_SCREENING_CINEMA_F foreign key (CinemaID, FilmID)
         references CINEMA_FILM (CinemaID, FilmID),
   constraint FK_SCREENIN_SCREENING_OCCURENC foreign key (OccurenceID)
         references OCCURENCE (OccurenceID)
);

CREATE VIEW VIEW_FILM_SCHEDULE AS
SELECT CF.CinemaID, CF.FilmID, ParticipantName as FilmName, CF.BeginValidity, CF.EndValidity, Datetime, DayOfWeek, SpecialDate,SCR.RoomID, PlaceName as RoomName
FROM CINEMA_FILM CF, SCREENING_TIME SCR, OCCURENCE O, PLACE P, ROOM R, PARTICIPANT P, FILM F
WHERE CF.FilmID = SCR.FilmID and CF.CinemaID = SCR.CinemaID and SCR.OccurenceID = O.OccurenceID and SCR.RoomID = R.RoomID and P.PlaceID = R.PlaceID and P.ParticipantID = F.ParticipantID and SCR.FilmID  = F.FilmID;

As i insert, there's no row in CINEMA_FILM , and in VIEW_FILM_SCHEDULE.
MetanilCommented:
Always check if data is available when selecting. try like this:

select count(*) into variable1 from dual where exists ( select BeginValidity,  EndValidity into fromD,toD
from  CINEMA_FILM
where CINEMA_FILM.FilmID = :new.FilmID);

if variable1 = 0 then
--- exit trigger with raising error
else
--do your task
end if;


Metanil
earth man2Commented:
If you tell us what line 10 is that would narrow it down.
You can catch the NO_DATA_FOUND exception like ...

begin
   select ...
exception
  when NO_DATA_FOUND then null;

end;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.