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 := 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)
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"?