dttai
asked on
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
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
here is my demo:
SQL> create table test(test_col raw(25));
Table created.
SQL> insert into test values(utl_raw.cast_to_raw
1 row created.
SQL> select * from test;
TEST_COL
--------------------------
74657374
SQL> create table test(test_col raw(25));
Table created.
SQL> insert into test values(utl_raw.cast_to_raw
1 row created.
SQL> select * from test;
TEST_COL
--------------------------
74657374
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
grant 300,
Did you mean for example
insert into testTable values (HEXTORAW('0xabcd');
Thanks,
Do
Did you mean for example
insert into testTable values (HEXTORAW('0xabcd');
Thanks,
Do
Yup. That's it.
Bill
Bill
ASKER
Grant30,
It didnt work
Here is what I did:
create table myTable(myId RAW(25)); //work fine
insert into myTable values(HEXTORAW('0x0909ab'
OR without the quote
insert into myTable values(HEXTORAW(0x0909ab))
It didnt work
Here is what I did:
create table myTable(myId RAW(25)); //work fine
insert into myTable values(HEXTORAW('0x0909ab'
OR without the quote
insert into myTable values(HEXTORAW(0x0909ab))
ASKER
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
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
ASKER
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
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
Yes, that's right. Oracle has its own peculiar way of interpreting and understanding RAW as Sybase manipulate BINARY data type.
ASKER
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
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
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(na
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(na
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
HEXTORAW in an intrinsic function and will run faster than going out to the UTL_RAW package to run a function there.
Bill
Bill
No, they are the same.
Oracle functions and stored procedures are the same breed
Oracle functions and stored procedures are the same breed
ASKER
grant and seazodiac,
Do these two commands store the same data in the table?
insert into myTable values(utl_raw.cast_to_raw
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
Do these two commands store the same data in the table?
insert into myTable values(utl_raw.cast_to_raw
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
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.
Bill.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
2)You have to use UTL_RAW.cast_to_raw() to convert your string to binary...