Home » SQL & PL/SQL » SQL & PL/SQL » Automatically converting all numbers to floats
Automatically converting all numbers to floats [message #386172] Fri, 13 February 2009 00:06 Go to next message
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 #386180 is a reply to message #386172] Fri, 13 February 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
No data type is specified so they all come out as NUMBER.

Not with Oracle tools. Datatype is mandatory.

FLOAT is NUMBER in Oracle.
You have to be more specific, for instance post a test case that we can reproduce.

Regards
Michel
Re: Automatically converting all numbers to floats [message #386213 is a reply to message #386172] Fri, 13 February 2009 02:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #386704 is a reply to message #386172] Mon, 16 February 2009 16:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>if field type = NUMBER then set field type = FLOAT
in what language is the line above written?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
Not valid in SQL or PL/SQL!

write SQL to write SQL

[Updated on: Mon, 16 February 2009 16:29]

Report message to a moderator

Re: Automatically converting all numbers to floats [message #386833 is a reply to message #386704] Tue, 17 February 2009 02:36 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
BlackSwan wrote on Mon, 16 February 2009 23:27
>if field type = NUMBER then set field type = FLOAT
in what language is the line above written?



That's called pseudo code, something widely used in ICT I believe Wink
Re: Automatically converting all numbers to floats [message #386845 is a reply to message #386703] Tue, 17 February 2009 02:47 Go to previous message
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?
Previous Topic: Pl/sql for Parallel processing
Next Topic: anonymous block
Goto Forum:
  


Current Time: Thu Feb 06 14:31:48 CST 2025