Thanks for the question, Priscillia.

Asked: February 14, 2001 - 4:57 am UTC

Last updated: July 26, 2004 - 5:12 pm UTC

Version: 7.3.2 & 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom!!

I have created two tables(test & test1) with columns data & data1 in Oracle 8.1.6. Declare data = 88 & data1 = 20 . Then execute:
select utl_raw.bit_and(utl_raw.cast_to_raw(a.data), utl_raw.cast_to_raw(b.data)) from test a, test1 b

The result shows: 3A3B

Example 2:
data = 20
data = 30

result = 3230
WHY????

CAn U pls intrepret ????

Moreover I found that if I execute
utl_raw.cast_to_raw(8)

the result shows: 38
&
A=41
G=47
O=4F

Why the results are not in terms of 1 & 0 (binary format)???

LAst but not least,
Can I perform the above mentioned statements in Trusted Oracle 7.2.3????
If not, what sggestions???? becos all my info are in Trusted Oracle ......

and Tom said...

You are mixing up datatypes and doing lots of IMPLICIT conversions. Lets look at the utl_raw.cast_to_raw(8) first.

that is really utl_raw.cast_to_raw('8') (the number is converted into a string -- cast to raw only does STRINGS). So, we now have RAW data '8'. When you select that in sqlplus, sqlplus converts the RAW into a VARCHAR -- it doesn't know how to display RAW data -- only strings. The rules for converting from RAW to VARCHAR says to take EACH byte of the RAW data and convert it into 2 hex characters. Hence the '8' which is ascii (decimal) 56 becomes hex 83. Here is the logical progression:

number 8 - implicitly becomes ->
string '8' - explicitly becomes ->
raw '8' - implicitly become -> varchar2 again

ops$tkyte@DEV816> select utl_raw.cast_to_raw(8) from dual;

UTL_RAW.CAST_TO_RAW(8)
---------------------------
38

ops$tkyte@DEV816> select to_number(38,'xx') from dual;

TO_NUMBER(38,'XX')
------------------
56

ops$tkyte@DEV816> select chr(56) from dual;

C
-
8

that shows it did exactly what you asked.

Now, the same thing happened for 88 and 20. They were turned into '88' and '20'. You converted them into raw -- and the STRINGS '88' and '20' were bitanded, not the numbers. It bitanded for example '8' and '0'. Now, we know that

'8' = 56 decimal = 111000 binary
'0' = 48 decimal = 110000 binary
-------
110000 binary = 48 decimal = 30 hex

When I do 88 and 20, i get 3030 as the answer (not 3a3b, don't know how you got that with that data)

select utl_raw.bit_and(utl_raw.cast_to_raw(88),utl_raw.cast_to_raw(20)) from dual
ops$tkyte@DEV816> /

UTL_RAW.BIT_AND(UTL_RAW.CAST_TO_RAW(88),UTL_RAW.CAST_TO_RAW(20))
----------------------------------------------------------------
3030

which matches my expectation totally.

To get binary, you can get my hexdec packages from
</code> http://asktom.oracle.com/~tkyte/hexdec/index.html <code>

Using that you can:

ops$tkyte@DEV816> set linesize 20
ops$tkyte@DEV816> l
1 select lpad(to_bin(88),20,'0'),
2 lpad(to_bin(20),20,'0'),
3 lpad(to_bin(bitand(88,20)),20,'0')
4* from dual
ops$tkyte@DEV816> /

LPAD(TO_BIN(88),20,'
--------------------
LPAD(TO_BIN(20),20,'
--------------------
LPAD(TO_BIN(BITAND(8
--------------------
00000000000001011000
00000000000000010100
00000000000000010000

which seems to be more like what you want.

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent

amol, February 14, 2002 - 12:47 pm UTC

You save me once again Tom

great script

sv, November 20, 2002 - 4:48 pm UTC

Thanks for sharing the code.
It helped me to code more efficiently and not invent the wheel again.

varchar2 to hex

Emad, July 12, 2003 - 9:05 am UTC

I have to convert a string to hex format in ucs2 encoding.
I have written a java program to do it, it works fine from command line, but when I load it into database I only get ? marks.
here is what I've written

public class t {
public static void main (String arr[])throws Exception {
System.out.println (
toHex(
new String (
arr[0].getBytes("Cp1252"), "Cp1256")
));

}

public static String toHex(String inStr) {
String retVal = "";
String tmp = "";
char buff [] = inStr.toCharArray();
for (int i = 0; i < buff.length; i++) {
tmp = Integer.toHexString(buff[i]);
int tmpLen = tmp.length();
if (tmpLen < 4) {
for (int k = tmpLen; k < 4; k++) {
tmp = "0" + tmp;
}
}
retVal += tmp;
}
return retVal;
}

}

My database character set is "AR8MSWIN1256".
DB version is 9.2, after upgrading from 7.3 to 9.2 we first decided to go for UTF8 encoding for out database but there were many problems, all our clients were developer 2000 and 6/6i whenever they connect to UTF-8 9.2 DB they just used to get crashed, so we reverted back to out charset of "AR8MSWIN1256".

The thing to note is that when I set my nls_lang
to AMERICAN_AMERICA.WE8ISO8859P1 and execute the java stored procedure from sql plus it works fine.
but I need to execute the code from an asp client by calling stored procedure.
when I connect from IIS it takes the nls_lang("ar8mswin") of the database and thus I get question marks when ever I execute it.
Is there any way to do this in pl/sql , for example
given a string in arabic "اثممخيخسف" should return me
"0627062b06450645062e064a062e06330641" ucs2 hex-encoded
string.
Hope I am clear in my explanation
Thanks
Emad

----------------------------
Your server is converting the arabic string into #encoded values.
----------------------------

utl_raw cast_to number

Arjun, July 12, 2004 - 11:34 am UTC

hi: Tom,

Thanks for the information on utl_raw, i am facing a problem with utl_raw.cast_to_number.

It works well in 9i , but 8i doesn't support cast_to_number

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Jul 12 11:28:30 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

Can you please suggest a workaround .

Thanks

Arjun

July 12, 2004 - 11:59 am UTC

guess you would have to give us an example of how you are using it, why you are using it.

purpose

arjun, July 13, 2004 - 11:30 am UTC

i am trying to encrypt a table in oracle database version 9.2.0.5 having number a varchar2 columns
CREATE TABLE A_ANUM
( SAL NUMBER, KEY VARCHAR2(100), BONUS NUMBER, NAME VARCHAR2(100) )

truncate table a_anum

insert into a_anum ( sal, bonus, name ) select rownum||1000, rownum||1000, table_name from user_tables;

the encryption is done using java class which returns RAW.

FUNCTION performEncrypt(Key VARCHAR2, inputString VARCHAR2) RETURN RAW
AS LANGUAGE JAVA
NAME 'EncryptDecrypt.performEncrypt(java.lang.String, java.lang.String) return byte[]';

TO encrypt varchar2 i use :

encryption key is generated using another java class.

FUNCTION generateKey RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'EncryptDecrypt.generateKey() return String';

declare
getkey_v varchar2(240);
begin
getkey_v := generatekey;
update a_anum set name = utl_raw.cast_to_varchar2(performEncrypt(getkey_v,name));

this works for varchar2.

for number

i use :

update a_anum
set
key = getkey_v ,
sal = utl_raw.cast_to_number (performEncrypt(getkey_v,sal)) ,
bonus = utl_raw.cast_to_number (performEncrypt(getkey_v,bonus));

it fails because the value returned is like : 2.08111105E-34, 2.08140305E-34....this raises either

ORA-01438: value larger than specified precision allows for this column or invalid number.

i tried using hextoraw/rawtohex but it didn't wotk..same errors.

Please suggest,

The same i want to do in 8.1.7

July 13, 2004 - 12:30 pm UTC

you cannot stuff raw into numbers, you cannot stuff raw into varchar2 (well, you can but watch out, it'll bite you really massively hard in the butt)

you need to drop that table and recreate the columns AS RAW!!!! you have raw data to store, not numbers, not dates, not strings.

utl_raw.cast_to_binary_integer

Valar G, July 23, 2004 - 11:47 am UTC

I have been trying to decode some raw data. The raw data is stored in a blob field of the database and is built up such that the first 4 bytes represent a number value namely bioid and the next 4 bytes represent a score, which is a float value. Using Oracle's utl_raw.cast_to_binary_integer, I am able to extract the actual data corresponding to the first 4 bytes, but when I apply the same function call for the next 4 bytes of the raw data, it returns me a very large number. Below is the select statement which illustrates this issue.

11:29:04 > r
1 select bio1id, dbms_lob.substr(reldata,4,1) MatchBioidRaw,
2 utl_raw.cast_to_binary_integer(dbms_lob.substr(reldata,4,1),2) ValueofMatchBioid,
3 dbms_lob.substr(reldata,4,5) MatchScoreRaw,
4 utl_raw.cast_to_binary_integer(dbms_lob.substr(reldata,4,5),2) ValueofMatchScore
5* from relation1 where bio1id = 1

BIO1ID
----------
MATCHBIOIDRAW
----------------------------------------------------------------------------------------------------
VALUEOFMATCHBIOID
-----------------
MATCHSCORERAW
----------------------------------------------------------------------------------------------------
VALUEOFMATCHSCORE
-----------------
1
2D0B0000
2861
994BFA3E
1056590745

11:29:05 >

11:32:16 > select utl_raw.cast_to_binary_integer('2D0B0000', 2) MatchBioid,
11:38:51 2 utl_raw.cast_to_binary_integer('994BFA3E', 2) MatchScore
11:39:07 3 from dual;

MATCHBIOID MATCHSCORE
---------- ----------
2861 1056590745

11:39:09 >

From the above query the raw value '2D0B0000' actually represents a bioid with value 2861. The second part of the raw value '994BFA3E' actually represents a float value of .488858. When I use the utl_raw.cast_to_binary_integer for the second part of the raw value it decodes as 1056590745.

Are there any methods in utl_raw which would allow to extract these float values ? I have been trying to use utl_bitand functions and so forth. Going no where so far. Any pointers in this regard would be appreciated.

Thanks,
Valar

July 23, 2004 - 4:26 pm UTC

binary integers are not IEEE floats. no way that would work.
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:681702012060 <code>
might work for you.

Valar G, July 26, 2004 - 5:12 pm UTC

Thanks for the followup Tom. Actually I did get to see the link which you mentioned a little later after I posted this review. The example and the attached Java stored procedures work fine for the sample data listed. However, when I try the same for the raw data in the test db, it always comes up with an error message as follows,

16:58:30 > r
1 select dbms_lob.substr( reldata, 4, 5 ) rfloat,
2 RAW_FLOAT_TO_NUMBER(dbms_lob.substr( reldata, 4 , 5)) xfloat
3* from relation1 where rownum < 5
from relation1 where rownum < 5
*
ERROR at line 3:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NumberFormatException:
994BFA3E

16:58:31 >

So tried a few modifications to the Java code, but couldn't get it to work yet.

Actually the current method uses C-based external stored procedures written in VC++ to convert the raw data to float values. Snippet of the code and the query is listed below,

// Function get_distance_from_relation
float get_distance_from_relation(unsigned char* rel,int ind,int size,int index)
{
float distance = -2;
int offset = (index - 1) * sizeof(rel_data);
if(offset < size)
{
memcpy(&distance,&rel[offset+sizeof(int)],sizeof(int));
}
return distance;
}

17:05:58 > r
1 select dbms_lob.substr( reldata, 4, 5 ) rfloat,
2 get_distance_from_relation(reldata,1) xfloat
3 from relation1 where rownum < 5
4*

RFLOAT
----------------------------------------------------------------------------------------------------
XFLOAT
----------
994BFA3E
.488858

2386093F
.537203

E65BEF3E
.467498

28B6EA3E
.458421

17:06:02 >

I was hoping to see if there was an equivalent in PL/SQL so that I could avoid the call to the external procedure.

Thanks again !!
Valar

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Excellent

great script

varchar2 to hex

utl_raw cast_to number

purpose

utl_raw.cast_to_binary_integer

More to Explore

PL/SQL demos

PL/SQL docs

decoding data in RAW after using utl_raw.cast_to_raw???

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Excellent

great script

varchar2 to hex

utl_raw cast_to number

purpose

utl_raw.cast_to_binary_integer

More to Explore

PL/SQL demos

PL/SQL docs