Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1623
  • Last Modified:

Orace raw type and Sybase binary type

hello experts,
I am wondering if the use of these two types are the same in Sybase 12.x and Oracle 10g

Say sybase would work if we do
insert into aSybaseTable (binaryType) values(0x80abcd)

but if we do that in oracle for a raw type say
insert into anOracleTable(rawType) values(80abcd; (or replace  80abcd with 0x80abcd)
then oracle complains: incosistent data type: expected BINARY got NUMBER

I guess what I am looking for is
1) what is the equivalent type of Sybase binary type in Oracle?
And
2) How would we insert a value into Oracle raw field?

Thanks,
Do
0
dttai
Asked:
dttai
  • 9
  • 6
  • 4
  • +1
3 Solutions
 
seazodiacCommented:
1)Oracle RAW data type will be the equivalent of BINARY in sybase.
2)You have to use UTL_RAW.cast_to_raw() to convert your string to binary...
0
 
seazodiacCommented:
here is my demo:

SQL> create table test(test_col raw(25));

Table created.

SQL> insert into test values(utl_raw.cast_to_raw('test'));

1 row created.

SQL> select * from test;

TEST_COL
--------------------------------------------------
74657374
0
 
grant300Commented:
I answered this for you over in the Sybase area.

Use the Oracle function HEXTORAW which converts your text string into a binary representation.

Bill
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
dttaiAuthor Commented:
grant 300,
Did you mean for example
insert into testTable values (HEXTORAW('0xabcd');
Thanks,
Do
0
 
grant300Commented:
Yup.  That's it.

Bill
0
 
dttaiAuthor Commented:
Grant30,
It didnt work

Here is what I did:

create table myTable(myId RAW(25)); //work fine

insert into myTable values(HEXTORAW('0x0909ab')); //---> error: ERROR at line 1: ORA-01465: invalid hex number

OR without the quote

insert into myTable values(HEXTORAW(0x0909ab)); //----> error: ERROR at line 1: ORA-00907: missing right parenthesis (which poiting to that char x)



0
 
dttaiAuthor Commented:
seazodiac,
Your suggestion doesn't have any error.
Anyway, my concern now is:
1) If I want to query a raw type, how should I do that?
SELECT * from test where test_col = utl_raw.cast_to_raw('test');
??
2) What base Orace uses to store raw number (e.x: binary base, decimal or hexadecimal???), while in Sybase as I understand it uses hexadecimal base, right?

Thanks,
Do
0
 
dttaiAuthor Commented:
seazodiac,
More on my previous point:
In Sybase, to insert a value to a binary field, we just need to prefix a hexadecimal string with 0x (e.x: 0xabc0980, etc.). Similarly, to query a binary field, we can use that same format.
My concern is: how are these operations (insert and select for example) done for a Raw field in Oracle? Is what you suggested the only way to do it?
Thanks,
Do
0
 
seazodiacCommented:
Yes, that's right. Oracle has its own peculiar way of interpreting and understanding RAW as Sybase manipulate BINARY data type.
0
 
dttaiAuthor Commented:
seazodiac,
So how '74657374' and 'test' are related?
I guess I understand from your previous post that it is the underlying structure, somehow Oracle converts that string 'test' to 74657374 to store it. I am just curious is it is possible for us to understand how that underlying process work to derive at the result 74657374? Is this representation platform-independent?
Thanks,
Do
0
 
seazodiacCommented:
well, the truth is that you don't want to know. Knowledge of that does not help you anyway.

as long as you know how to use UTL_RAW package to manipulate RAW and VARCHAR, it's sufficient.


to get back what you have:

select * from test where ult_raw.cast_to_varchar(name)='test';

0
 
vn77Commented:
>>I guess I understand from your previous post that it is the underlying structure, somehow Oracle converts that string 'test' to 74657374 to store it. I am just curious is it >>is possible for us to understand how that underlying process work to derive at the result 74657374? Is this representation platform-independent?

Hi,
askTom has some relevant info for your above question:
http://asktom.oracle.com/pls/ask/f?p=4950:8:1023863916058250673::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1455804517846,

vn
0
 
dttaiAuthor Commented:
grant,
after changing 0x0909ab to 0909ab, it worked. thanx.

To all:
what are the differences between using hextoraw and utl_raw.cast_to_raw here?

BTW, thanks to vn77, I found that link very interesting.

Thanks,
Do
0
 
grant300Commented:
HEXTORAW in an intrinsic function and will run faster than going out to the UTL_RAW package to run a function there.

Bill
0
 
seazodiacCommented:
No, they are the same.
Oracle functions and stored procedures are the same breed
0
 
dttaiAuthor Commented:
grant and seazodiac,
Do these two commands store the same data in the table?

insert into myTable values(utl_raw.cast_to_raw('0ABCD'));
and
insert into myTable values(hextoraw('0ABCD'));

where myTable is created as follows: create table myTable (myId RAW(25));

Thanks,
Do

P/S: I ask the question since when I query the table after the insertions, I got two different results:
select * from myTable;

returned the following:
OABCD;// for hextoraw insertion
3041424344; //for cast_to_raw insertion



0
 
grant300Commented:
If you compare the two results you got back, you will see that the "cast_to_raw" is returning the ASCII character values as a string of five two digit numbers.  I don't think that is what you want.

Bill.
0
 
seazodiacCommented:
well, the truth is that cast_to_raw() and hextoraw() will both convert the input to RAW data type.

but the caveat here is the INPUT, they take different INPUT ..

cast_to_raw takes STRING (Varchar), whereas hextoraw() takes HEX values.

check this out:

I knew this from a demo:
     
 the hex value '48656C6C6F' = 'Hello' (string)


so if you do this:

insert into myTable values(hextoraw('48656C6C6F'));


select  utl_raw.cast_to_varchar(myId) from myTable will return "Hello"...


0
 
dttaiAuthor Commented:
Well,
Thanks everyone. I think I am going to close this thread since my original post has been answered (in an impressive way). I think everybody here is deserving some award points. So I am going to do a split with more going to the accepted answer.
Thanks everyone and hope to see you at my next question :),
Do
0
 
dttaiAuthor Commented:
Sorry seazodiac,
I meant for the accepted answer to go to you. Anyway you should get most portion of the points (please double check). If you are not satisfied, please let me know.

To admin: is there anyway I can change the accepted answer? Thanks.
Do
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now