Improve company productivity with a Business Account.Sign Up

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

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

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
karthikd22
Asked:
karthikd22
1 Solution
 
suganthkumar1Commented:
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
 
karthikd22Author Commented:
Hi Suganth,
Thanks.That worked.Once again thanks for your help.

Regards,
Karthik
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now