Solved

Orace raw type and Sybase binary type

Posted on 2004-10-06
20
1,594 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
  • 9
  • 6
  • 4
  • +1
20 Comments
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

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

Expert Comment

by:grant300
Comment Utility
Yup.  That's it.

Bill
0
 

Author Comment

by:dttai
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
No, they are the same.
Oracle functions and stored procedures are the same breed
0
 

Author Comment

by:dttai
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now