Re: UTL_RAW binary encoding - 2

From: neilsolent <n_at_solenttechnology.co.uk>
Date: Tue, 16 Jul 2013 22:37:20 -0700 (PDT)
Message-ID: <6e135fe0-dc56-4e94-967f-67482f9664bb_at_googlegroups.com>

On Wednesday, 17 July 2013 03:54:08 UTC+1, kooro..._at_gmail.com wrote:
> On Tuesday, July 16, 2013 5:22:26 AM UTC+10, neilsolent wrote:
>
> > I seek a function similar to utl_raw.cast_from_binary_integer [see examples below] BUT I need it to:
>
> >
>
> > 1. Work with 8-byte numbers (not limited to 4 bytes)
>
> >
>
> > 2. Work with unsigned integers (negative numbers do not take up 1/2 of the "range"):
>
> >
>
> >
>
> >
>
> > Does anyone know how to create such a function?
>
> >
>
> >
>
> >
>
> > Thanks,
>
> >
>
> > Neil
>
> >
>
> >
>
> >
>
> >
>
> >
>
> > SQL> select utl_raw.cast_from_binary_integer(2147483647) from dual;
>
> >
>
> >
>
> >
>
> > UTL_RAW.CAST_FROM_BINARY_INTEGER(2147483647)
>
> >
>
> > --------------------------------------------------------------------------------
>
> >
>
> > 7FFFFFFF
>
> >
>
> >
>
> >
>
> > SQL> select utl_raw.cast_from_binary_integer(2147483648) from dual;
>
> >
>
> >
>
> >
>
> > UTL_RAW.CAST_FROM_BINARY_INTEGER(2147483648)
>
> >
>
> > --------------------------------------------------------------------------------
>
> >
>
> > 7FFFFFFF
>
> >
>
> >
>
> >
>
> > SQL> select utl_raw.cast_from_binary_integer(-1) from dual;
>
> >
>
> >
>
> >
>
> > UTL_RAW.CAST_FROM_BINARY_INTEGER(-1)
>
> >
>
> > --------------------------------------------------------------------------------
>
> >
>
> > FFFFFFFF
>
>
>
> What about using the TO_CHAR function converting to hex ?
>
>
>
> SQL> select TO_CHAR(2147483648,'XXXXXXXX') from dual;
>
>
>
> TO_CHAR(2
>
> ---------
>
> 80000000
>

This is not converting to raw, it is converting to char. If you look at the raw bytes they will be 56 48 48 48 48 48 48 48 - these are the ASCII codes for 8 0 0 0 0 0 0 0. Whereas I want the true raw binary representation - literally the bytes 8 0 0 0 0 0 0 0. It is confusing because sqlplus will render these to look the same.

>
> Otherwise there are many options for base conversion functions available

Like? I don't see any function to output raw binary representation of 8-byte usnigned integers.

This is one such function that does work that I have just written myself, however this looks highly inefficient, there must be something faster and lower-level than this:

create or replace function raw8(int8 in number) return raw as begin
declare

	a number;
	b number;
	c number;
	d number;
	
	begin
		a := bitand(int8, 65535);
		b := bitand((int8 - a) / 65536, 65535);
		c := bitand((((int8 - a) / 65536) - b) / 65536, 65535);
		d := bitand((((((int8 - a) / 65536) - b) / 65536) - c) / 65536, 65535);
		
		return (utl_raw.concat(utl_raw.substr(utl_raw.cast_from_binary_integer(d), 3, 2),
			utl_raw.substr(utl_raw.cast_from_binary_integer(c), 3, 2),
			utl_raw.substr(utl_raw.cast_from_binary_integer(b), 3, 2),
			utl_raw.substr(utl_raw.cast_from_binary_integer(a), 3, 2)));
	end;

end;
/

Is there any way of looking at the source code for utl_raw ?

thanks - Neil Received on Wed Jul 17 2013 - 07:37:20 CEST