Automatically converting all numbers to floats [message #386172] |
Fri, 13 February 2009 00:06  |
philb
Messages: 10 Registered: February 2009
|
Junior Member |
|
|
Hi,
I have inherited some SQL that generates and populates a few hundred fields to a table. No data type is specified so they all come out as NUMBER.
I have some external app that doesn't deal with the NUMBER data type, unless precision has been specified.
My question - can anyone point me in the direction of some PL/SQL code that will scan all the fields in a table and convert all NUMBER formats to FLOAT? Is this possible?
My other option is to modify the sql and cast everything as a float in the first place - but I would like a smarter reusable way of doing this.
Thanks in advance.
|
|
|
|
Re: Automatically converting all numbers to floats [message #386213 is a reply to message #386172] |
Fri, 13 February 2009 02:47   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I think that CAST is the function you are after:SQL> create table test_100 as (select level col_1
2 ,sqrt(level) col_2
3 ,mod(level,4) col_3
4 from dual connect by level <= 5);
Table created.
SQL>
SQL> desc test_100;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL_1 NUMBER
COL_2 NUMBER
COL_3 NUMBER
SQL>
SQL>
SQL> create table test_101 as (select cast(col_1 as float) col_1
2 ,cast(col_2 as float) col_2
3 ,cast(col_3 as float) col_3
4 from test_100);
Table created.
SQL>
SQL> desc test_101;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL_1 FLOAT(38)
COL_2 FLOAT(38)
COL_3 FLOAT(38)
|
|
|
Re: Automatically converting all numbers to floats [message #386703 is a reply to message #386213] |
Mon, 16 February 2009 16:18   |
philb
Messages: 10 Registered: February 2009
|
Junior Member |
|
|
Thanks, I know I could modify the SQL to use cast, but it means manually editing a lot of code all over the place.
I was wondering if there might be some way of scanning the table field definitions and changing the field type this way.
for each field in table
if field type = NUMBER then set field type = FLOAT
Is there any such way or am I being too ambitious.
Phil
|
|
|
|
|
Re: Automatically converting all numbers to floats [message #386845 is a reply to message #386703] |
Tue, 17 February 2009 02:47  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Your best bet might well be to recreate the table that you insert into.
Number -> Float is a loss of accuracy, so you can't just change the column type while there's data in the table - you have to either create a new table, or have a spare column to hold the data while you empty the column and change the type.
Have you considered creating a view on this table that would simply cast the data as Floats, and then letting your app look at the view instead of the table?
|
|
|