Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

RAW Data column to VARCHAR2.UTL_RAW.CAST_TO_VARCHAR2 didn't work?

Posted on 2004-08-13
2
Medium Priority
?
2,217 Views
Last Modified: 2010-07-27
Hello All,
I am tying to migrate data from some existing DB tables to another.I came across a  strange problem in converting a RAW datatype to VARCHAR2/NUMBER.

The source table has got one column(friend_group_ids) which is of RAW(500) datatype.I found in the PL/SQL script which populates the values to the table as follows (snipped unnecessary code):
 friend_group_id number :=101000000
 group_id varchar2(10);

 group_id := NumbertoHex(friend_group_id, 16,8);

NumbertoHex is a user defined function which converts this number to Hex(101000000 => 06052340).

The INSERT statement in PL/SQL procedure is as follows:
insert into tst_friends (NUMERIC_ID , FRIEND_GID ,  NUM_OF_GROUPS, FRIEND_GROUP_IDS)
      values ( NUMERIC_ID , friend_gid,1 , group_id );

I understand this is possible,since it works.But is it correct to store varchar2 in RAW datatype.

Since I don't have the access to the actual tables, I created the table with the same schema structure but to insert the sample values I used the following INSERT statement:
insert into ODG_FRIENDS
(NUMERIC_ID, FRIEND_GID, NUM_OF_GROUPS, FRIEND_GROUP_IDS)
VALUES
(12003,'user05@foo.com',1,'06052340');

The INSERT was successful.

But I wanted to convert the friend_group_ids to "VARCHAR2"  back to do some manipulation  before I store the data in another table.I tried UTL_RAW.CAST_TO_VARCHAR2(friend_group_ids) but it produced some ascii characters.

May I know how can I get the column data value as "06052340"?

Thanks
Karthik

0
Comment
Question by:karthikd22
[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
2 Comments
 

Accepted Solution

by:
suganthkumar1 earned 500 total points
ID: 11807270
hi,

declare
 v varchar2(100);
begin
 select rawtohex(col1) into v from temp;
 dbms_output.put_line(v);
end;
/

using this way, you can get the raw data in the form of hex value represented in varchar2 datatype.

when you use the UTL_RAW package, it does an ascii character mapping. that is why you got the junk characters

Have a hice day
Suganth
0
 

Author Comment

by:karthikd22
ID: 11808862
Hi Suganth,
Thanks.That worked.Once again thanks for your help.

Regards,
Karthik
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

670 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