Ошибка в триггере “ORA-06502: PL/SQL: numeric or value error: character to number conversion error” при сравнении только HH24:MI

#sql #oracle #plsql

Необходимо создать триггер, который будет проверять, что время начала не раньше 09:00
и не позже 18:00. Tриггер создался, но при внесении данных выводятся  такие ошибки:  


  ORA-06502: PL/SQL: numeric or value error: character to number conversion error
      ORA-06512: at "DECANAT.CORRECT_TIMETIBLE", line 2
      ORA-04088: error during execution of trigger 'DECANAT.CORRECT_TIMETIBLE'


Триггер:

CREATE OR REPLACE TRIGGER Correct_TimeTible
INSTEAD of Insert ON Timetable1
BEGIN
if  to_char(:new.DATE_BEGIN, 'hh24:mi') >= to_char('9:00', 'hh24:mi') and 
to_char(:new.DATE_BEGIN, 'hh24:mi') <= to_char('16.30', 'hh24:mi') 
then Insert into Timetable values (:new.ID_TIMETABLE,:new.ID_SUBJECT,:new.ID_TEACHER,:new.ID_TYPE,:new.DATE_BEGIN);
ELSE dbms_output.put_line('Incorrected time');
end if;
END;


Создание таблицы TIMETABLE:

CREATE TABLE  "TIMETABLE" 
(   "ID_TIMETABLE" NUMBER, 
"ID_SUBJECT" NUMBER, 
"ID_TEACHER" NUMBER, 
"ID_TYPE" NUMBER, 
"DATE_BEGIN" DATE, 
 CONSTRAINT "TIMETABLE_PK" PRIMARY KEY ("ID_TIMETABLE") ENABLE, 
 CONSTRAINT "SUB_TIME_FK" FOREIGN KEY ("ID_SUBJECT")
  REFERENCES  "SUBJECT" ("ID_SUBJECT") ENABLE, 
 CONSTRAINT "TEACH_TIME_FK" FOREIGN KEY ("ID_TEACHER")
  REFERENCES  "TEACHER" ("ID_TEACHER") ENABLE, 
 CONSTRAINT "TYPE_TIME_FK" FOREIGN KEY ("ID_TYPE")
  REFERENCES  "TYPE_SUBJECT" ("ID_TYPE") ENABLE
)
/


Вставка для проверки работы триггера:

insert into Timetable1 (ID_TIMETABLE,ID_SUBJECT,ID_TEACHER,ID_TYPE,DATE_BEGIN) 
values (SEQ_TIMETABLE.nextval,'1','3','1',to_date('20.05.2018 15:00','dd.mm.yyyy
hh24:mi')
);

    

Ответы

Ответ 1

Замените условие в триггере на:

if  to_char(:new.date_begin, 'hh24mi') >= '0900' and 
    to_char(:new.date_begin, 'hh24mi') <= '1630'
then 
    ...




Или можно сравнить как разницу в часах и минутах (чисел) завернув преобразование
в функцию, например:

create or replace function toMinuteOfDay (dt date) return number is
begin
    return extract(hour from cast (dt as timestamp))*60 + extract(minute from cast
(dt as timestamp)); 
end;
/
select toMinuteOfDay (to_date('20.05.2018 16:30', 'dd.mm.yyyy hh24:mi')) "MinuteOfDay" 
from dual;

MinuteOfDay
------------
       990


Но никакого преимущества в производительности такое сравнение не даст.