Long to Long Raw

[shailesh]

Hello,

On Oracle 8.1.7.x, Windows 2000.
We want to transfer data from column with data type LONG to Column with data type LONG RAW. Is this possible?

If yes could you please guide me to do the same.

Thanks & Regards,

Shailesh

[tamilselvan]

I do not think converting LONG to LONG RAW will work in Oracle.
The reason LONG RAW is in binary format.

You can try the following function to convert long to long raw, see if it works.

create or replace function chartoraw return long raw as 
rawdata long raw; 
rawlen number; 
hex varchar2(32760); 
v_char varchar2(32760); 
i number; 
begin 
   select <long column> into v_char from <table>; 
   rawlen:=length(v_char); 
   i:=1; 
   while i<rawlen 
   loop 
      hex:=numtohex(ascii(substr(v_char,i,1))); 
      rawdata:=rawdata || hextoraw(hex); 
      i:=i+1; 
   end loop; 
   return rawdata; 
end; 



create or replace FUNCTION numtohex(v_hex number)
return varchar2 
as 
hex varchar2(4); 
num1 number; 
num2 number; 
begin 
   num1 := trunc(v_hex/16); 
   num2 := v_hex-(num1*16); 
   if ( num1 >= 0 and num1 <= 9 ) then 
      hex := hex||to_char(num1); 
   end if; 
   if num1 = 10 then hex := hex||'A'; end if; 
   if num1 = 11 then hex := hex||'B'; end if; 
   if num1 = 12 then hex := hex||'C'; end if; 
   if num1 = 13 then hex := hex||'D'; end if; 
   if num1 = 14 then hex := hex||'E'; end if; 
   if num1 = 15 then hex := hex||'F'; end if; 
   if ( num2 >= 0 and num2 <= 9 ) then 
      hex := hex||to_char(num2); 
   end if; 
   if num2 = 10 then hex := hex||'A'; end if; 
   if num2 = 11 then hex := hex||'B'; end if; 
   if num2 = 12 then hex := hex||'C'; end if; 
   if num2 = 13 then hex := hex||'D'; end if; 
   if num2 = 14 then hex := hex||'E'; end if; 
   if num2 = 15 then hex := hex||'F'; end if; 
   return hex; 
end;
/ 

Credit must go to S.Krishna Kumar.

Please note that the procedure will work only if the column length is <= 32K. Otherwise you have to use Pro*C / OCI calls.

Basically the logic is same - read a character and convert into hex.

Also, you can try this:

select utl_raw.cast_to_raw('B') B,  
       utl_raw.cast_to_raw('A') A from dual
SQL> /

B          A
---------- ----------
42         41 

Tamil

[OracleDoc]

Just as an FYI you might want to consider changing the data type to a blob or clob instead. Oracle is trying to steer away from long /long raw data types. Besides, you can do more with blobs than long.