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_i d, 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(f riend_grou p_ids) but it produced some ascii characters.
May I know how can I get the column data value as "06052340"?
Thanks
Karthik
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_i
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,
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(f
May I know how can I get the column data value as "06052340"?
Thanks
Karthik
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.That worked.Once again thanks for your help.
Regards,
Karthik