DB version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL:
create table test1(col1 timestamp with time zone);
create or replace trigger test1_biu
BEFORE INSERT OR UPDATE
ON test1
FOR EACH ROW
BEGIN
:new.col1 := FROM_TZ(TO_TIMESTAMP(:new.col1, 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),'UTC');
END;
/
insert into test1 values('2009-10-14T00:00:00Z');
Error report -
SQL Error: ORA-01843: not a valid month
01843. 00000 - "not a valid month"
*Cause:
*Action:
I am getting the above error when i try to insert the row to the table. However below SQL working exactly as expected. Tried in both TOAD and SQL Developer tools. Getting same error. My observation is trigger is not even firing.
select FROM_TZ(TO_TIMESTAMP('2009-10-14T00:00:00Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),'UTC') col1 from dual;
create or replace trigger test1_biu
BEFORE INSERT OR UPDATE
ON test1
FOR EACH ROW
BEGIN
raise_application_error(-20001,'Trigger Fired');
END;
/
insert into test1 values('2009-10-14T00:00:00Z');
Error report -
SQL Error: ORA-01843: not a valid month
01843. 00000 - "not a valid month"
*Cause:
*Action:
Failing server validation for data type conversion errors?Can some one throw some light?
Thanks!
