OraFAQ Forum: SQL & PL/SQL » ORA-01861: literal does not match format string

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01861: literal does not match format string

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01861: literal does not match format string [message #232077]

Thu, 19 April 2007 05:09

BillyJim
Messages: 3
Registered: April 2007
Location: Oslo

Junior Member

Hello!

I am really sorry to bother you with this but I am still unsure how to correct this error.

ORA-01861: literal does not match format string
ORA-06512: at "SCHEMA.TRIGGERNAME", line 87
ORA-04088: error during execution of trigger 'SCHEMA.TR_TIGGERNAME'
ORA-02063: preceding 3 lines from DBLINK$USER

What is freaks out on is:
,TO_CHAR(TO_DATE(:NEW.PORTING_DATE,'DD.MM.RRRR')), 'Porting_Date');

The PORTING_DATE is a DATE column.

Any obvious error in the TO_CHAR(TO_DATE conversion??

Thanks alot.

Kind regards,
Andreas

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #232080 is a reply to message #232077]

Thu, 19 April 2007 05:15

Maaher
Messages: 7065
Registered: December 2001

Senior Member

BillyJim wrote on Thu, 19 April 2007 12:09

The PORTING_DATE is a DATE column.

BillyJim wrote on Thu, 19 April 2007 12:09

TO_CHAR(TO_DATE(:NEW.PORTING_DATE,'DD.MM.RRRR'))

If Porting date is already a date, there's no need to cast it to a date again (TO_DATE). A TO_CHAR will do:

TO_CHAR(:NEW.PORTING_DATE,'DD.MM.RRRR')

MHE

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #232082 is a reply to message #232080]

Thu, 19 April 2007 05:27

BillyJim
Messages: 3
Registered: April 2007
Location: Oslo

Junior Member

Ok.

Sorry Maaher.

Was abit rushed in my first post here..Appologize for not formatting code and not explaining good enough.

PORTING_DATE is where it reads the value.
It is trying to insert it into a VARCHAR2 column.

INSERT INTO letteruser.customer_letter_values
            (customer_letter_value_id
           , letter_value_id
           , customer_letter_id
           , VALUE
           , NAME
            )
VALUES      (letteruser.customer_letter_values_pk_seq.NEXTVAL
           , 4
           , letteruser.customer_letter_pk_seq.CURRVAL
           , TO_CHAR (TO_DATE (:NEW.porting_date, 'DD.MM.RRRR'))
           , 'Porting_Date'
            );

[Updated on: Thu, 19 April 2007 05:53] by Moderator

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #232088 is a reply to message #232082]

Thu, 19 April 2007 05:58

Maaher
Messages: 7065
Registered: December 2001

Senior Member

But you stated that :NEW.porting_date is a DATE. If that is the fact, loose the TO_DATE.

If you want the end result formatted, you might want to add a date format to your TO_CHAR otherwise you'll get the default date format (that is not fixed).

MHE

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #232105 is a reply to message #232088]

Thu, 19 April 2007 08:01

BillyJim
Messages: 3
Registered: April 2007
Location: Oslo

Junior Member

Thanks mate.

Did a TO_CHAR(TRUNC(:NEW.PORTING_DATE

Works like a charm.
Was abit worried and therefor the many questions since I had to change in a production enviroment and I didnt create the trigger in the first place..

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #232947 is a reply to message #232077]

Tue, 24 April 2007 04:02

seraphstream
Messages: 8
Registered: March 2007

Junior Member

Have you see?

Oracle Errors ORA-01861

bye!

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #232951 is a reply to message #232947]

Tue, 24 April 2007 04:26

Frank
Messages: 7901
Registered: March 2000

Senior Member

Have you seen this?

Quote:

Thanks mate.
...
Works like a charm.

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #232962 is a reply to message #232951]

Tue, 24 April 2007 05:02

shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai

Senior Member

hi,

@seraphstream>>>>
just a gentle reminder.

i remember frank asking you this question for one of your previous posts.

Quote:

Why are you answering all sorts of questions that already have sufficient answers?

If you want to promote your forum, do it in the open and not by spamming this forum please.

regards
shanth

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #233043 is a reply to message #232962]

Tue, 24 April 2007 07:54

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Excellent shanth for remembering that. I would advise people NOT to go to that site anyways. One of the error messages he posted referred to a setting in Oracle 5. Yes, Oracle 5!

Report message to a moderator

Re: ORA-01861: literal does not match format string [message #233073 is a reply to message #233043]

Tue, 24 April 2007 09:23

shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai

Senior Member

thanks joy_division.

regards,
shanth

[Updated on: Tue, 24 April 2007 09:30]

Report message to a moderator

Previous Topic:	How to get Oracle sid?
Next Topic:	remote_os_authent???!!!!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 13 05:32:42 CST 2024