Implicit conversion of date field in a pl/sql trigger?

I need to develop a trigger in PL/SQL (Oracle) before INSERT.

In this table there 开发者_JAVA百科is a column (cdat) of type DATE.

Let's say i do INSERT INTO myTbl (123,'12/05/2011');

In my trigger the :NEW.CDAT is converted in the final date system or it's still a varchar?

Do I need to do a TO_DATE(:NEW.CDAT) to get the date value?

---

:NEW.CDAT will be a date. The :new and :old variables in triggers are always the type of the destination field.

---

I wasn't able to find anything in the Oracle documentation that confirms my statement, but I was able to devise some experimental proof:

CREATE TABLE test2 (a DATE);

CREATE OR REPLACE TRIGGER bu_test2
   BEFORE INSERT OR UPDATE
   ON test2
   FOR EACH ROW
DECLARE
   PROCEDURE type_test(in_type DATE) IS
   BEGIN
      DBMS_OUTPUT.put_line('date');
   END;
   PROCEDURE type_test(in_type VARCHAR2) IS
   BEGIN
      DBMS_OUTPUT.put_line('varchar2');
   END;
BEGIN
   type_test(:new.a);
END;

INSERT INTO test2
  VALUES   ('24-Mar-2011');

Since type_test is overloaded, Oracle will choose which procedure to use based on the type being passed in. The results of this script are:

Table created.
Trigger created.
date
1 row created.

---

You need to do a conversion if your session parameter 'NLS_DATE_FORMAT' is not 'mm/dd/yyyy'.

For example:

create table    myTbl  (id number, cdat date);
select *
from   nls_session_parameters ns
where  ns.parameter = 'NLS_DATE_FORMAT';

PARAMETER                    VALUE
-------------------------------------------------------
NLS_DATE_FORMAT              DD-MON-RR

In this case, without a to_Date you'll get an error:

insert into myTbl
values
    (123, '12/05/2011');

ORA-01843: not a valid month

You can change this paramter at session level, system level, etc.

zep@dev> alter session set NLS_DATE_FORMAT = 'mm/dd/yyyy';

Session altered

select *
from   nls_session_parameters ns
where  ns.parameter = 'NLS_DATE_FORMAT';

PARAMETER                VALUE
-------------------------------------------------------------------------------------
NLS_DATE_FORMAT          mm/dd/yyyy

insert into myTbl
    (id, cdat)
values
        (123, '12/05/2011');

1 row inserted

zep@dev> select *
      2  from   myTbl;

        ID CDAT
---------- -----------
       123 05/12/2011

---

Test on a row level trigger

truncate table Mytbl;
alter session set NLS_DATE_FORMAT = 'DD-MON-RR';       
create or replace trigger befins_myTbl
    before insert on myTbl
    for each row
declare
begin
    -- demo
    :new.cdat := :new.cdat + numtoyminterval(1,'YEAR');-- (demo trigger add 1 year )
end;

insert into myTbl
(id, cdat)
values
(123, '12/05/2011');

Output: ORA-01843: not a valid month

alter session set NLS_DATE_FORMAT = 'mm/dd/yyyy';

insert into myTbl
    (id, cdat)
values
    (123, '12/05/2011');
commit;

select *
from   myTbl;

Output

ID         CDAT
---------- -----------
123        12/05/2012