Re: Force implicit data conversion

We definitely agree to avoid implicit conversions :-) The discussion now is mostly how to understand the docs, which unfortunately aren't always as clear as we could wish :-(

Let me pick two lines from the doc quotation you gave:

*Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around.*

and:

*During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.*

OK.

The first line talks about IF the column is implicitly converted and implies that it is possible that it happens the other way around. The second line it's a matter of how you define "target variable."

"Target variable" isn't much used in Oracle docs: https://docs.oracle.com/apps/search/search.jsp?q=%22target%20variable%22&start=0&pg=1&category=database&product=e11882-01&size=40#tab1

But my understandment of "target variable" in any programming language is a *target* of an assignment.
Where it is used in PL/SQL (second hit in the search link above), the terms target variable and source variable clearly is for assignment.

So I understand the second doc line above in the way that "target variable" is the "target of assignment", that is the line is talking about implicit conversion of the columns in the list between SELECT and FROM.

*IF* it could be understood that "target variable" also could be a (bind) variable or literal in a comparison in the WHERE clause, then my tests at least show that this is not true. (Then we would have a doc bug ;-) My tests show that sometimes the column is implicitly converted to the variable/literal datatype, sometimes the other way around. This fits the first doc line above that also mentions "the other way around."

So that's all a matter of how to understand the wording of the documentation. If we understand the wording the way I claim, then it fits the facts when tested. If not, then it would be a doc bug IMHO. So I choose to believe that the docs in this case are correct, but perhaps a bit vague/unclear in the wording (which is not unusual for the docs ;-).

What I *cannot* find in the docs is corroboration for when I state the conversion happens from "lower precision" to "higher precision". I'm certain I have read it somewhere, but if it was the docs, I can't find it again. It could have been om AskTom, but again I've searched and not found it.

It *may* be that it is not meant to be documented, it *may* be what is hidden in the doc statements:

*Implicit conversion depends on the context in which it occurs and may not work the same way in every case.*
*...*
*Algorithms for implicit conversion are subject to change across software releases and among Oracle products.*

So I cannot *prove* I'm correct, I can just show tests that seem to work as I expect, and I can say that it is the way that logically makes most sense, as if it wasn't so, then that would mean "incorrect results" could happen from a query.

There - that's my reasoning and defence :-)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

On Thu, Mar 5, 2015 at 9:10 PM, Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:

> Evening Kim,
>
> If you don't mind me asking, where did you find those definitions of
> "target variable"? I'm unable to find anything that Oracle have documented
> as such, but maybe I'm not searching properly - it happens!
>
> I agree I mentioned literal, when maybe I shouldn't have, mea culpa as
> they say in Ancient Rome.
>
> There were comments earlier on this thread that mentioned something along
> the lines of "Oracle wouldn't convert the table data to match the camp
> arisen data, that would be a huge performance hit" (obviously I'm
> paraphrasing, I'm in the bath ant not at my laptop!). However, the extract
> from the docs I quoted shows quite clearly, that Oracle will convert the
> table data.
>
> I think, however, we all agree, implicit conversions are pretty much
> misunderstood, unhealthy and, generally, a bad thing.
>
>
>
> Cheers,
> Norm.
>
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>

--
http://www.freelists.org/webpage/oracle-l