Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Timestamp with Timezone conversion -- not a valid month error

sgudipudiAug 31 2017 — edited Sep 1 2017

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!

This post has been answered by sgudipudi on Aug 31 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 29 2017
Added on Aug 31 2017
11 comments
1,694 views