Home » SQL & PL/SQL » SQL & PL/SQL » Binary Integer Division (Oracle 11g R2)
| Binary Integer Division [message #675039] |
Wed, 06 March 2019 07:49  |
 |
jagman
Messages: 8
Registered: March 2019
|
Junior Member |
|
|
I'm trying to perform a right-bit-shift on a binary integer in PL/SQL.
This involves simply dividing the integer by a power of 2.
The problem is, the integer arithmetic is not performed as expected.
In C, the following snippet prints 55, which is what I would expect.
Integers don't have a decimal portion, so the result is effectively truncated, i.e 55.5 becomes 55.
#include <stdio.h>
void main ( void ) {
unsigned int x = 111;
unsigned int y = x / 2;
printf("%i\n", y);
}
55
But a similar snippet in PL/SQL prints 56!
DECLARE
x binary_integer := 111;
y binary_integer;
BEGIN
y := x / 2;
dbms_output.put_line( y );
END;
/
56
An implicit conversion to a real must be happening somewhere.
How can I get around this?
One solution would be to subtract 1 from the value before performing the division (providing its not 0), but this is ugly.
Does anyone know of a better solution?
|
|
|
|
| Re: Binary Integer Division [message #675040 is a reply to message #675039] |
Wed, 06 March 2019 07:58  |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
No idea why you expected it to be truncated. I would expect it to be rounded, which is what happened.
If you want the result truncated then apply the trunc or floor functions to it.
SQL> DECLARE
2 x binary_integer := 111;
3 y binary_integer;
4 BEGIN
5 y := trunc(x / 2);
6 dbms_output.put_line( y );
7 y := FLOOR(x / 2);
8 dbms_output.put_line( y );
9 END;
10 /
55
55
|
|
|
|
|
|
| Re: Binary Integer Division [message #675042 is a reply to message #675041] |
Wed, 06 March 2019 08:41 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I did - but that's C.
In oracle you do the math and then work out what you can fit in the variable.
Which is to say it doesn't make any difference what type x is unless you are trying to put decimal data in there as well.
I guess C just works differently.
Anyway - floor or trunc is what you need.
|
|
|
|
|
|
|
|
| Re: Binary Integer Division [message #675050 is a reply to message #675047] |
Wed, 06 March 2019 10:48 |
|
jagman
Messages: 8
Registered: March 2019
|
Junior Member |
|
|
I was fooled by reading this excerpt from
https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#LNPLS319
Its clearly put there to deceive C programmers like me... 
Quote:
PLS_INTEGER and BINARY_INTEGER Data Types
The PL/SQL data types PLS_INTEGER and BINARY_INTEGER are identical. For simplicity, this document uses PLS_INTEGER to mean both PLS_INTEGER and BINARY_INTEGER.
The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.
The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes:
PLS_INTEGER values require less storage.
PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.
For efficiency, use PLS_INTEGER values for all calculations in its range.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Binary Integer Division [message #675060 is a reply to message #675058] |
Thu, 07 March 2019 00:56 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:that PL/SQL [...] is not performing integer
This is correct they chose to perform NUMBER arithmetic to have no difference between PLS_INTEGER/BINARY_INTEGER and INTEGER types.
What would you then say if a language has 2 (or 3) integer types (for history reason) with different results? 
I once wrote, in the previous millennium, a package for bit manipulation on Oracle INTEGER type (not hardware one so slower but can use bits strings/integers larger than 32/64 bits):
SQL> exec dbms_output.put_line(bit_functions.bit_rsh(111));
55
PL/SQL procedure successfully completed.
create or replace package bit_functions as
function int_to_bits (in_val in integer) return varchar2;
function bits_to_int (in_val in varchar2) return integer;
function bit_and (in_1 in integer, in_2 in integer) return integer;
function bit_or (in_1 in integer, in_2 in integer) return integer;
function bit_xor (in_1 in integer, in_2 in integer) return integer;
function bit_lsh (in_num in integer,
in_shft in integer default 1) return integer;
function bit_rsh (in_num in integer,
in_shft in integer default 1) return integer;
end;
/
show errors;
create or replace package body bit_functions as
function int_to_bits (in_val in integer) return varchar2
is
work_1 number;
work_2 number;
work_3 number;
digit varchar2(1);
accum varchar2(64);
begin
if in_val < 0 or trunc(in_val) != in_val then
raise_application_error(-20000, 'Invalid input value');
end if;
accum := '';
work_1 := in_val;
work_2 := in_val;
loop
work_2 := work_1/2;
work_3 := trunc(work_2);
if work_2 = work_3 then digit := '0';
else digit := '1'; end if;
accum := digit || accum;
if work_3 = 0 then exit; end if;
work_1 := work_3;
end loop;
return accum;
end;
function bits_to_int (in_val in varchar2) return integer
is
i number;
digit number;
accum number;
begin
accum := 0;
for i in 1..length(in_val) loop
digit := to_number(substr(in_val,i,1));
accum := 2*accum + digit;
end loop;
return accum;
end;
function bit_and (in_1 in integer, in_2 in integer) return integer
is
work_1 varchar2(64);
work_2 varchar2(64);
digit number;
i number;
accum number;
begin
if in_1 < 0 or trunc(in_1) != in_1 or in_2 < 0 or trunc(in_2) != in_2 then
raise_application_error(-20000, 'Invalid input value');
end if;
work_1 := int_to_bits (in_1);
work_2 := int_to_bits (in_2);
accum := 0;
if length(work_1) > length(work_2) then
work_2 := lpad(work_2,length(work_1),'0');
end if;
if length(work_2) > length(work_1) then
work_1 := lpad(work_1,length(work_2),'0');
end if;
for i in 1..length(work_1) loop
if substr(work_1,i,1) = '1' and substr(work_2,i,1) = '1' then
digit := 1;
else
digit := 0;
end if;
accum := 2*accum + digit;
end loop;
return accum;
end;
function bit_or (in_1 in integer, in_2 in integer) return integer
is
work_1 varchar2(64);
work_2 varchar2(64);
digit number;
i number;
accum number;
begin
if in_1 < 0 or trunc(in_1) != in_1 or in_2 < 0 or trunc(in_2) != in_2 then
raise_application_error(-20000, 'Invalid input value');
end if;
work_1 := int_to_bits (in_1);
work_2 := int_to_bits (in_2);
accum := 0;
if length(work_1) > length(work_2) then
work_2 := lpad(work_2,length(work_1),'0');
end if;
if length(work_2) > length(work_1) then
work_1 := lpad(work_1,length(work_2),'0');
end if;
for i in 1..length(work_1) loop
if substr(work_1,i,1) = '1' or substr(work_2,i,1) = '1' then
digit := 1;
else
digit := 0;
end if;
accum := 2*accum + digit;
end loop;
return accum;
end;
function bit_xor (in_1 in integer, in_2 in integer) return integer
is
work_1 varchar2(64);
work_2 varchar2(64);
digit number;
i number;
accum number;
begin
if in_1 < 0 or trunc(in_1) != in_1 or in_2 < 0 or trunc(in_2) != in_2 then
raise_application_error(-20000, 'Invalid input value');
end if;
work_1 := int_to_bits(in_1);
work_2 := int_to_bits(in_2);
accum := 0;
if length(work_1) > length(work_2) then
work_2 := lpad(work_2,length(work_1),'0');
end if;
if length(work_2) > length(work_1) then
work_1 := lpad(work_1,length(work_2),'0');
end if;
for i in 1..length(work_1) loop
if substr(work_1,i,1) = substr(work_2,i,1) then
digit := 0;
else
digit := 1;
end if;
accum := 2*accum + digit;
end loop;
return accum;
end;
function bit_lsh (in_num in integer, in_shft in integer default 1)
return integer
is
begin
if in_num < 0 or trunc(in_num) != in_num or in_shft < 0 or trunc(in_shft) != in_shft then
raise_application_error(-20000, 'Invalid input value');
end if;
return power(2,in_shft)*in_num;
end;
function bit_rsh (in_num in integer, in_shft in integer default 1)
return integer
is
begin
if in_num < 0 or trunc(in_num) != in_num or in_shft < 0 or trunc(in_shft) != in_shft then
raise_application_error(-20000, 'Invalid input value');
end if;
return trunc(in_num/power(2,in_shft));
end;
end;
/
show errors [Updated on: Thu, 07 March 2019 00:59] Report message to a moderator |
|
|
|
|
|
| Re: Binary Integer Division [message #675066 is a reply to message #675062] |
Thu, 07 March 2019 08:13 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
It is funny to watch how people react when they end up in a situation where something doesn't work they are used to. Many label it "wrong" right away. Others label it "different". Do you say "it is wrong" when you come to UK and cars drive on the left side of the road? Same way does person from UK say "it is wrong" about cars driving on the right side of the road? Cars have to drive on some side, right? Same way is assigning fraction to an integer. No matter what we have to do something with fractional part. Some tools say lose it completely some say round it up so we lose less. Personally, I am for losing up to .499...9 versus losing .999...9. Now I support both Oracle & SQL Server. Oracle rounds SQL Server truncates. I don't label either of them right or wrong. I simply accept the rules.
SY.
|
|
|
|
| Re: Binary Integer Division [message #675071 is a reply to message #675058] |
Thu, 07 March 2019 15:30 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
i disagree with the other systems and agree with oracle.
111/2 = 55.5
Following the industry standard .5 rounding rule it goes to 56.
however if you want to duplicate your results then simply throw away the remainder.
trunc(111/2)
floor(111/2)
In effect the other systems are simply truncating the remainders anyway. In Oracle you have a choice wither or not you want to use the remainder.
|
|
|
|
Goto Forum:
Current Time: Thu May 22 10:28:42 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
