Home » SQL & PL/SQL » SQL & PL/SQL » IMPLICIT CONVERSION (Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 )
IMPLICIT CONVERSION [message #441734] Tue, 02 February 2010 23:33 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
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"?
Re: IMPLICIT CONVERSION [message #441736 is a reply to message #441735] Tue, 02 February 2010 23:45 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
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'
Re: IMPLICIT CONVERSION [message #441740 is a reply to message #441737] Wed, 03 February 2010 00:15 Go to previous messageGo to next message
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?
Re: IMPLICIT CONVERSION [message #441742 is a reply to message #441740] Wed, 03 February 2010 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: IMPLICIT CONVERSION [message #441744 is a reply to message #441740] Wed, 03 February 2010 00:24 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
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
Re: IMPLICIT CONVERSION [message #441745 is a reply to message #441734] Wed, 03 February 2010 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: IMPLICIT CONVERSION [message #441747 is a reply to message #441742] Wed, 03 February 2010 00:27 Go to previous messageGo to next message
mohley_s
Messages: 10
Registered: January 2010
Location: bangalore, india
Junior Member
sorry my mistake,

the statement was select concat(123,456) from dual;
Re: IMPLICIT CONVERSION [message #441748 is a reply to message #441747] Wed, 03 February 2010 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: IMPLICIT CONVERSION [message #441749 is a reply to message #441734] Wed, 03 February 2010 00:28 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: IMPLICIT CONVERSION [message #441753 is a reply to message #441749] Wed, 03 February 2010 00:31 Go to previous messageGo to next message
mohley_s
Messages: 10
Registered: January 2010
Location: bangalore, india
Junior Member
thankyou frank,

this explanation is some what convincing.

regards,
mohley
Re: IMPLICIT CONVERSION [message #441771 is a reply to message #441753] Wed, 03 February 2010 01:14 Go to previous message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
And the conclusion is: never rely on implicit conversion.
Previous Topic: Problem with Materialized View compile state
Next Topic: problem with SQL CREATE TABLE
Goto Forum:
  


Current Time: Mon Sep 26 19:45:27 CDT 2016

Total time taken to generate the page: 0.10788 seconds