OraFAQ Forum: SQL & PL/SQL » IMPLICIT CONVERSION

Home » SQL & PL/SQL » SQL & PL/SQL » IMPLICIT CONVERSION (Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 )

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

IMPLICIT CONVERSION [message #441734]

Tue, 02 February 2010 23:33

mohley_s
Messages: 10
Registered: January 2010
Location: bangalore, india

Junior Member

Hi All,

(I am fresher to oracle database)
i have a small doubt..

oracle does implicit conversion of data types when we call some function. Implicit conversion to date happens for some given format.

I executed the SQl -

SELECT TO_CHAR('23-JUL-09','DD-MM-RR') FROM DUAL;

AND GOT ERROR:ORA-01722: invalid number

Why implict conversion is not taking place in this case.

NLS_DATE_FORMAT was DD-MON-RR

Please someone clear my doubt

thankyou,
Mohley Singh

[Updated on: Wed, 03 February 2010 00:20] by Moderator

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441735 is a reply to message #441734]

Tue, 02 February 2010 23:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>SELECT TO_CHAR('23-JUL-09','DD-MM-RR') FROM DUAL;
You provided format mask that did not match input string.

How/what should be done with "JUL" to use mask "MM"?

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441736 is a reply to message #441735]

Tue, 02 February 2010 23:45

mohley_s
Messages: 10
Registered: January 2010
Location: bangalore, india

Junior Member

SELECT TO_CHAR('23-JUL-09','DD-MON-RR') FROM DUAL;

This is also giving the same error

[Updated on: Tue, 02 February 2010 23:45]

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441737 is a reply to message #441736]

Tue, 02 February 2010 23:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

TO_CHAR() is used to convert DATE to "string"
TO_DATE() is used to convert "string" to DATE.

It makes no sense to use TO_CHAR with a "string"; like '23-JUL-09'

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441740 is a reply to message #441737]

Wed, 03 February 2010 00:15

mohley_s
Messages: 10
Registered: January 2010
Location: bangalore, india

Junior Member

when we give sql

select concat('123','345') from dual;

here also it makes no sense, but still implicit conversion from charecter to number is taking place.

so, why not in this(SELECT TO_CHAR('23-JUL-09','DD-MON-RR') FROM DUAL;) statement.

and when does implicit conversion from charecter to date happens or oracle does not support this implict conversion?

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441742 is a reply to message #441740]

Wed, 03 February 2010 00:22

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

select concat('123','345') from dual;

here also it makes no sense, but still implicit conversion from charecter to number is taking place.

Wrong! Why do want Oracle make implicit conversion to concatenate 2 strings when you give 2 strings.
There is NO conversion.

Regards
Michel

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441744 is a reply to message #441740]

Wed, 03 February 2010 00:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>and when does implicit conversion from charecter to date happens or oracle does not support this implict conversion?

If variable END_DATE is type DATE, then implicit conversion occurs
END_DATE = '2010-02-03';

The value left of the equal sign is a string.
The variable to the right is a DATE.

Implicit conversion occurs across equal sign with conversion functions like TO_DATE or TO_CHAR

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441745 is a reply to message #441734]

Wed, 03 February 2010 00:25

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You should read:
Database SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Section Datatype Comparison Rules
Paragraph Data Conversion

Regards
Michel

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441747 is a reply to message #441742]

Wed, 03 February 2010 00:27

mohley_s
Messages: 10
Registered: January 2010
Location: bangalore, india

Junior Member

sorry my mistake,

the statement was select concat(123,456) from dual;

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441748 is a reply to message #441747]

Wed, 03 February 2010 00:28

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

mohley_s wrote on Wed, 03 February 2010 07:27

sorry my mistake,

the statement was select concat(123,456) from dual;

And the question is?

Regards
Michel

Report message to a moderator

Re: IMPLICIT CONVERSION [message #441749 is a reply to message #441734]

Wed, 03 February 2010 00:28

Frank
Messages: 7901
Registered: March 2000

Senior Member

The cause of the error, despite what everyone is telling you, is that Oracle actually IS trying to do an implicit conversion.
However, to_char can take either a number or a date as (first) parameter. Oracle guessed wrong about which of the two you meant and tried to implicitly convert your string to a number.

Report message to a moderator