CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldevelopermainframe
blackberry dev
blackberry dev

Reputation: 371

Round off float in oracle 11g upto 2 places for all users

I want to insert float value into a column of type float in a table. But value coming from Cobol/Mainframe system after calculation is 5.4999999 but i want to insert 5.50 or 5.49 into oracle table without using round function. So is there any way to set a float value upto 2 decimal places for all the users in oracle 11g or like some setting done by SYSDBA for all the user or Default number value upto 2 or n number of decimal places ? Thanks in advance.

Upvotes: 1

Views: 3548

Answers (4)

Alex Poole
Alex Poole

Reputation: 191265

If you can't change the insert code to add a round() call, can't change the value being passed into the insert, and can't change the table structure... then you could use the view replacement Ben suggests; or adjust the data as it's queried directly form the table; or add a trigger to round the value as it is inserted:

create table t42 (id integer, value float);

-- value inserted with more precision than you want
insert into t42 values (1, 5.4999999);

create or replace trigger tr42
before insert or update on t42
for each row
begin
  :new.value := round(:new.value, 2);
end;
/

-- value inserted with two decimal places of precision
insert into t42 values (2, 5.4999999);

select * from t42 order by id;

        ID      VALUE
---------- ----------
         1  5.4999999 
         2        5.5

SQL Fiddle demo. Using a trigger for something like this seems like overkill, but if you've really exhausted all other avenues then it might be something to consider.

If you wanted to get 5.49 when 5.4999999 is inserted then you could use trunc(value, 2) instead:

create or replace trigger tr42
before insert or update on t42
for each row
begin
  :new.value := trunc(:new.value, 2);
end;
/

insert into t42 values (3, 5.4999999)
/

select * from t42 order by id;

        ID      VALUE
---------- ----------
         1  5.4999999 
         2        5.5 
         3       5.49

Upvotes: 1

Ben
Ben

Reputation: 52853

It's impossible to define a constant "format" in which people will always view numbers.

If you can't alter the table due to legacy issues the obvious thing to do would be to create a view on the table in order to transform the data into what you want. You could rename the original table and name the view the same as the original table in order to avoid changing all of your code. Test this first.

rename my_table to my_table_float

create or replace view my_table as
 select <some_columns>
      , cast(my_float_column as number(36, 2)) as my_float_column
   from my_table_float

This solution strikes me as slightly dangerous - you'll need to know exactly what code uses the column and ensure that this code doesn't expect a float. Personally, I'm always more in favour of transforming the data as and when you need it rather than assuming it needs to be in a certain format before it's used.

Upvotes: 4

RandiiRedneck
RandiiRedneck

Reputation: 11

You can create your table using a column defined as a number with precision and scale.

create table temp_foo (test number(4,2));

This means that column test will be a maximum of 4 digits in length, with 2 after the decimal place.

Then inserting the following...

insert into temp_foo values(54.345);

And then selecting...

select * from temp_foo;

Will result in a value of 54.35

If you want to allow a bigger number then increase the precision to say 10 and keep the scale at 2 - NUMBER(10,2) - now you can insert 12345678.234535 and you'll get 12345678.23 back out.

In summary, create your table to hold the data in the format your require. Let Oracle do the hard work :)

Hope that helps.

Upvotes: 1

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

No, there's no such thing as "setting a float value up to 2 decimal places for all users in Oracle".

You may either alter the target table column to number(*,2) or use the rounding.

Upvotes: 1

Related Questions