oracle to_binary,Binary_double to Number and varchar2 conversion issue

I ran into the BINARY_DOUBLE to NUMBER problem myself.

The correct solution is to ROUND() to the appropriate significant digits.Use LOG() to count how many digits you have,

Then calculate which decimal you need to ROUND() to.

Then ROUND() to that value.

Like this.....

(not FULLY tested)

MK

create or replace

function sig_round( p_x in number, p_sig in int)

return number

deterministic

as

begin

if p_sig < 1

then

raise invalid_number;

end if;

return round( p_x, p_sig - ceil(log(10,abs(p_x))));

end;

/

with data  as (

select 1.2345678901 x from dual union all

select 12345678.901 from dual union all

select 1.2345678901 from dual union all

select 12345678901 from dual union all

select .000012345678901 from dual union all

select -1.2345678901 x from dual union all

select -12345678.901 from dual union all

select -1.2345678901 from dual union all

select -12345678901 from dual union all

select -.000012345678901 from dual

)

select x, sig_round(x,6) r_x,log(10, abs( x)) log_x, ceil(log(10,abs(x))) N, 6 - ceil(log(10,abs(x))) as round_me from data;