Oracle - Cast Varchar to Float and specify the precision

Question

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

I'm trying to create a materialized view on top of a pre-built table. Because of this, all the data types must match exactly...including the precision and size of the data types. The original column (before the NVL was added) was pulling from a FLOAT data type with a precision of 126. When I try casting a varchar to a float of a precision of 126 it doesn't seem to include the 126 data precision.

(I tested the fact that it wasn't including the 126 data size by creating a standard view with the below query that casts to float(126). Through my Toad IDE I could see that the precision of the "ThisorThat" float column was 38).

I have simply updated my materialized view with a NVL statement like so...

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

I get the error "Ora-12060: shape of prebuilt table does not match definition query" because the sizes are different than the original table that I am "pre-building" upon. I need to somehow cast the varchar to a float with an explicit size of 126. Any ideas?

Version: Oracle 10g

Edit: Here's a link which is basically the same thing I'm encountering.

Solution

Use

TO_BINARY_FLOAT(mynumberstring) 

or

TO_BINARY_DOUBLE(mynumberstring) 

What you're trying to do actually isn't a cast, it's a conversion.