javasql-serverhierarchyid

Migrate data type hierarchyid from SQL Server to Oracle VARCHAR2

I am migrating data from SQL Server to Oracle in a specified format. I have a table in SQL Server that has a column of type hierarchyid. I want to convert it into string, without changing its look. For example, if the hierarchyid type contain 0x78, I want it to convert to string type "0x78". How can I do it? I have the freedom to use SQL Server and/or Java to do this conversion. Thanks in advance.

Solution 
  • declare @hid hierarchyid = '/1/1/1/';

select 
    v1 = convert(varchar(1000), cast(@hid as varbinary(892)), 1), -- = 0x5AD6
    v2 = convert(varchar(1000), cast(@hid as varbinary(892)), 2); -- = 5AD6