Solved

Orace raw type and Sybase binary type

Posted on 2004-10-06
20
1,609 Views
Last Modified: 2012-06-27
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
Comment
Question by:dttai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 4
  • +1
20 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12243325
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12243342
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
 
LVL 19

Assisted Solution

by:grant300
grant300 earned 50 total points
ID: 12243730
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:dttai
ID: 12243832
grant 300,
Did you mean for example
insert into testTable values (HEXTORAW('0xabcd');
Thanks,
Do
0
 
LVL 19

Expert Comment

by:grant300
ID: 12243960
Yup.  That's it.

Bill
0
 

Author Comment

by:dttai
ID: 12244087
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
 

Author Comment

by:dttai
ID: 12244135
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
 

Author Comment

by:dttai
ID: 12244187
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12244202
Yes, that's right. Oracle has its own peculiar way of interpreting and understanding RAW as Sybase manipulate BINARY data type.
0
 

Author Comment

by:dttai
ID: 12244388
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12244505
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
 
LVL 2

Accepted Solution

by:
vn77 earned 150 total points
ID: 12245533
>>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
 

Author Comment

by:dttai
ID: 12246232
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
 
LVL 19

Expert Comment

by:grant300
ID: 12252142
HEXTORAW in an intrinsic function and will run faster than going out to the UTL_RAW package to run a function there.

Bill
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12252362
No, they are the same.
Oracle functions and stored procedures are the same breed
0
 

Author Comment

by:dttai
ID: 12252653
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
 
LVL 19

Expert Comment

by:grant300
ID: 12252751
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
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 300 total points
ID: 12252956
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
 

Author Comment

by:dttai
ID: 12256458
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
 

Author Comment

by:dttai
ID: 12256482
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question