Geek asks and answers

Oracle - enable varchar for float and specify precision

I need to cast varchar on a float. (Varchar is guaranteed to be a number)

I am trying to create a materialized view on top of a pre-built table. Because of this, all data types must exactly match ... including the accuracy and size of data types. The original column (before adding NVL) pulled out of the FLOAT data type with an accuracy of 126. When I try to use varchar for a float with an accuracy of 126, it does not seem to include the accuracy of the data 126.

(I tested the fact that it did not include the data size 126, creating a standard view with the following query that throws float (126). Through my IDE Toad system, I could see that the accuracy of the “ThisorThat” floating column was 38).

I just updated my materialized view with an NVL instruction, for example ...

Select Cast(NVL(thisFloat, thatFloat) as Float(126)) as ThisorThat
....
From tables;

I get the error "Ora-12060: the shape of the finished table does not match the definition request" because the sizes are different from the original table that I "pre-create". I need to somehow turn the varchar into a float with an explicit size of 126. Any ideas?

Version: Oracle 10g

Edit: Here is a link that is basically the same thing that I come across.

+3
source share
1 answer

Use

TO_BINARY_FLOAT(mynumberstring)

or

TO_BINARY_DOUBLE(mynumberstring)

What you are actually trying to do is not a translation, but a transformation.

+2
source

More articles:


All Articles