solve err in before insert trigger on pl/sql

Question

I create table b

create table b(
id number(3),
name varchar2(5),
birth_day date
);

& I create trigger p

create or replace trigger p
 before insert on b
 for each row 
 declare
 g number;
 begin
 SELECT MONTHS_BETWEEN(TO_DATE(to_char (sysdate,'DD-MM-YYYY')), TO_DATE (to_char(:new.BIRTH_day,'DD-MM-YYYY')))/12 
   INTO g FROM DUAL;
     IF (g< 18) THEN
      RAISE_APPLICATION_ERROR(-20000,'VV');
 
    END IF; 
      IF (g> 18) THEN
      RAISE_APPLICATION_ERROR(-20000,'ll');
      end if;
    end;

When entering data in the b table birth_day row '08/09/2001' It is executed p trigger but When entering data in the b table birth_day row '08/09/2000' It does not run p trigger & This error is shown: 'error ORA-01841: (full) year must be between -4713 and +9999, and not be 0' So that I can when I type '08/09/2000' p trigger is executed what should I do?

I want when type 08/09/2000 p trigger is execute

Answer 1

Too many manoeuvres with dates; why? Dates should be handled as dates, don't make things complicated. 08/09/2001 is a string; use date literal (as my example shows) or to_date function with appropriate format model.

SQL> create or replace trigger p
  2   before insert on b
  3     for each row
  4  declare
  5    g number;
  6  begin
  7     g := months_between(trunc(sysdate), :new.birth_day) / 12;
  8
  9    IF (g< 18) THEN
 10        RAISE_APPLICATION_ERROR(-20000,'VV');
 11    END IF;
 12
 13    IF (g> 18) THEN
 14        RAISE_APPLICATION_ERROR(-20000,'ll');
 15    end if;
 16  end;
 17  /

Trigger created.

Testing:

SQL> insert into b (id, name, birth_day) values
  2    (1, 'Mike', date '1970-02-03');
insert into b (id, name, birth_day) values
            *
ERROR at line 1:
ORA-20000: ll
ORA-06512: at "SCOTT.P", line 11
ORA-04088: error during execution of trigger 'SCOTT.P'


SQL> insert into b (id, name, birth_day) values
  2    (1, 'Mike', date '2022-02-03');
insert into b (id, name, birth_day) values
            *
ERROR at line 1:
ORA-20000: VV
ORA-06512: at "SCOTT.P", line 7
ORA-04088: error during execution of trigger 'SCOTT.P'


SQL> insert into b (id, name, birth_day) values
  2    (1, 'Mike', date '2005-03-11');

1 row created.

SQL>

---

Testing your date value; when you said 08/09/2001, it is unclear whether it is 8th of September (dd/mm/yyyy) or 9th of August (mm/dd/yyyy) so - here you are, both options. Result is VV in both cases.

SQL> insert into b (id, name, birth_day) values
  2    (1, 'Mike', date '2020-09-08');
insert into b (id, name, birth_day) values
            *
ERROR at line 1:
ORA-20000: VV
ORA-06512: at "SCOTT.P", line 7
ORA-04088: error during execution of trigger 'SCOTT.P'


SQL> insert into b (id, name, birth_day) values
  2    (1, 'Mike', date '2020-08-09');
insert into b (id, name, birth_day) values
            *
ERROR at line 1:
ORA-20000: VV
ORA-06512: at "SCOTT.P", line 7
ORA-04088: error during execution of trigger 'SCOTT.P'

---

No problem with year 2000 either (but outcome is now different because of number of years):

SQL> insert into b (id, name, birth_day) values
  2    (1, 'Mike', date '2000-08-09');
insert into b (id, name, birth_day) values
            *
ERROR at line 1:
ORA-20000: ll
ORA-06512: at "SCOTT.P", line 11
ORA-04088: error during execution of trigger 'SCOTT.P'


SQL>

Once again: value you try to insert, literally '08/09/2000' is a string; it is enclosed into single quotes. You rely on Oracle's capabilities to implicitly convert that string into a valid DATE datatype value. Why DATE? Because you created table that way:

create table b(
id          number(3),
name        varchar2(5),
birth_day   date              --> this; column's datatype is DATE
);

Therefore, you should insert appropriate datatype value into that column - a DATE.

You can use

• date literal, which consists of date keyword followed by value enclosed into single quotes in format yyyy-mm-dd
  • example: date '2023-03-11' is today's date, 11th of March 2023
• to_date function whose format model follows value you pass
  • example: to_date('11.03.2023', 'dd.mm.yyyy')

You should not try to insert a string in any format. Oracle might, or might not be able to recognize it. If it doesn't, it'll return error. If it succeeds, code on your computer might work, but porting that application to another database whose NLS settings are different from the ones on your previous server will end up with an error. Now imagine bunch of code which relies in implicit conversion ... you'll be VERY SORRY you thought that passing strings to DATE datatype columns was a good idea. It was not, it is not, and it will not be, ever.