Link to home
Start Free TrialLog in
Avatar of karthikd22
karthikd22

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of suganthkumar1
suganthkumar1

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of karthikd22
karthikd22

ASKER

Hi Suganth,
Thanks.That worked.Once again thanks for your help.

Regards,
Karthik