java将oracle十六进制字符串转换为实际字符串值

Question

我已经从Oracle Logminor中提取了数据，您需要将其转换为有用的sql。以下是数据

SQL_REDO from logminer:
insert into "UNKNOWN"."OBJ# 74587"("COL 1","COL 2","COL 3","COL 4","COL 5","COL 6","COL 7","COL 8","COL 9") 
values (HEXTORAW('c12d'),HEXTORAW('c4032c362e'),HEXTORAW('c3042222'),HEXTORAW('c105'),HEXTORAW('c3394c44'),HEXTORAW('c108'),HEXTORAW('c108'),HEXTORAW('c109'),HEXTORAW('c10b'));

Required Value:
Insert Into Scott.Test_Table
Select 44,2435345,33333,4.1234,567567,6.766,7,8,10 From Dual;

So I need help with the conversion from the Hex value to the actual value, this is for all data types.

for example:
c12d = 44
c4032c362e = 2435345
etc

下面是我试图转换的java代码，但它并没有完全将十六进制转换为字符串

public class StringToHex{

  public String convertStringToHex(String str){

      char[] chars = str.toCharArray();

      StringBuffer hex = new StringBuffer();
      for(int i = 0; i < chars.length; i++){
        hex.append(Integer.toHexString((int)chars[i]));
      }

      return hex.toString();
  }

  public String convertHexToString(String hex){

      StringBuilder sb = new StringBuilder();
      StringBuilder temp = new StringBuilder();

      //49204c6f7665204a617661 split into two characters 49, 20, 4c...
      for( int i=0; i<hex.length()-1; i+=2 ){

          //grab the hex in pairs
          String output = hex.substring(i, (i + 2));
          //convert hex to decimal
          int decimal = Integer.parseInt(output, 16);
          //convert the decimal to character
          sb.append((char)decimal);

          temp.append(decimal);
      }
      System.out.println("Decimal : " + temp.toString());

      return sb.toString();
  }

  public static void main(String[] args) {

      StringToHex strToHex = new StringToHex();
      System.out.println("\n***** Convert ASCII to Hex *****");
      String str = "2435345";  
      System.out.println("Original input : " + str);

      String hex = strToHex.convertStringToHex(str);

      System.out.println("Hex : " + hex);

      System.out.println("\n***** Convert Hex to ASCII *****");
      System.out.println("Hex : " + hex);
      System.out.println("ASCII : " + strToHex.convertHexToString(hex));
  }
}

请建议我如何理解Oracle十六进制代码以将其转换为实际字符串。根据上面的代码，它不是根据我上面提到的字符串

Answer 1

我不知道为什么要提取原始值，但这些是the Oracle number datatype的二进制表示，它与Java中的任何数据类型都不对应

通过使用UTL_原始包，您可以看到该值的来源：

select rawtohex(utl_raw.cast_from_number(44)) from dual;           C12D 
select rawtohex(utl_raw.cast_from_binary_integer(44)) from dual;   2C = 101100 = 44 
select rawtohex(utl_raw.cast_to_raw('44')) from dual;              3434 = ASCII "4" "4"

如果要将C12D从Oracle数字转换为十进制表示，可以使用UTL_RAW.CAST_TO_NUMBER或you can do it the hard way：

C12D = 11000001 00101101

对于第一个字节，第一1位表示它是一个正数。剩下1000001 = 65，我们减去64得到1，所以指数是100^1

对于第二个字节00101101 = 45，减去1得到44。所以最后的数字是0.44 x 100^1，或44

我不会再讨论小数点或负数了，你可以自己读

我觉得你应该用UTL_RAW.CAST_TO_NUMBER

select utl_raw.cast_to_number(hextoraw('C12D')) from dual;   44

Answer 2

我已经找到了解决方案，请查看此代码

using System;

public class num2raw
{  
   public static void Main()
   {


        Decimal p_number = Decimal.Parse("44");
        string v_result = "";
        Int16 v_exponent;
        Int16 v_bcd;
        Decimal v_temp;

        if (p_number == 0) 
            v_result = "80";
        else if (p_number > 0) {
            v_temp = p_number;
            v_exponent = Convert.ToInt16(193 + Math.Floor(Math.Log(Convert.ToDouble(p_number),100)));

            while (v_temp != Math.Floor(v_temp))
                v_temp *= 100;

            while (v_temp % 100 == 0) 
                v_temp = Math.Floor(v_temp/100);

            while (v_temp > 0) {
                v_bcd = Convert.ToInt16(v_temp % 100);
                v_result = String.Format("{0:X2}", v_bcd+1) + v_result;
                v_temp = Math.Floor(v_temp/100);
            }

            v_result = String.Format("{0:X2}", v_exponent) + v_result;
        }
        else { // p_number < 0
            v_temp = -p_number;
            v_exponent = Convert.ToInt16(62 - Math.Floor(Math.Log(Convert.ToDouble(v_temp),100)));

            while (v_temp != Math.Ceiling(v_temp))
                v_temp *= 100;

            while (v_temp % 100 == 0) 
                v_temp = Math.Floor(v_temp/100);            

            while (v_temp > 0) {
                v_bcd = Convert.ToInt16(v_temp % 100);
                v_result = String.Format("{0:X2}", 101 -v_bcd) + v_result;
                v_temp = Math.Floor(v_temp/100);
            }

            v_result = String.Format("{0:X2}", v_exponent) + v_result;

            if (v_result.Length < 42)
                v_result = v_result + "66";
        }

        Console.WriteLine(v_result);

   }
}