Re: substr, substrb and raw datatype

From: HansF <News.Hans_at_telus.net>
Date: Tue, 14 Jun 2005 19:25:17 GMT
Message-Id: <pan.2005.06.14.19.28.16.821829@telus.net>

On Tue, 14 Jun 2005 20:34:57 +0200, Rafal Dabrowa interested us by writing:

> HansF wrote:

>> The Oracle documentation also says:
>> [...]
>> "
>> When Oracle automatically converts RAW or LONG RAW data to and from CHAR
>> data, the binary data is represented in hexadecimal form with one
>> hexadecimal character representing every four bits of RAW data. For
>> example, one byte of RAW data with bits 11001011 is displayed and entered
>> as 'CB.'
>> "

>
> That is what I expected from both substr and substrb. Both take
> "char" as first parameter, so, I thought that this implicit conversion
> is performed for both functions. If I invoke substrb as follows:
>
> substrb( rawtohex(val), 5, 2)

Again, from the documentation,

"
RAWTOHEX converts raw to a character value containing its hexadecimal equivalent. The raw argument can be either RAW or BLOB datatype. "

so we are doing a conversion. Then applying the substrB to the result.

>
> results are the same as for
>
> substr( val, 5, 2)
>
> I'm surprised that such implicit conversion does not take place for
> substrb, although for substr - does. Also, such behavior is

substr does a character-oriented substring. Conversion *must* take place to get a character string.

OTOH, substrb does a byte-oriented substring. As such, I see absolutely no need to convert to any form of character. All you are asking is to hack out something starting at byte 'x'.

Why do you want/expect/hope for a conversion with substrB?

> inconsistent with behavior of length/lengthb function pair.
> Both functions return the same value for raw fields.

Assuming your database/session is set to a single byte character set (and assuming this will influence the results) length() and lengthb() should provide the same result - one character = one byte.

>

>> But, one may ask WHY you are doing character-based substrings of binary
>> data?  (Rhetorical question <g>)

>
> In fact, I want to extract a part of a raw column. I haven't found
> in Oracle documentation any such function for raw datatype. But,
> substrb function does exactly what I want. Although, theoretically
> (in my opinion), should not.

Why does the 'take the data as a byte-string and don't interpret anything' not work the way you expect?

> I want to know, whether the function behaves really correctly, or
> whether is this an Oracle bug, which may be corrected in future.

In that case, log a TAR. You do have a support contract, don't you??? <g> (After all, you can't expect Oracle to honour anything said here by non-Oracle volunteers.)

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***

Received on Tue Jun 14 2005 - 14:25:17 CDT