将16进制的数据转换为字符



将oracle 16进制的数据转换为字符类型

SQL> create or replace function hextostr(hexstr varchar2) return varchar2 is

    i    number;

    s    char;

    str1 varchar2(20);

    rst  varchar2(200);

  begin

    i   := 1;

    rst := '';

    Loop

     str1 := substr(replace(hexstr, ' '), i, 2);

     select chr(to_number(str1, 'xxxxxxxxxx')) into s from dual;

     rst := rst || s;

     exit when i > lengthb(hexstr);

     i := i + 2;

   end loop;

   return rst;

 end;

 /

二、测试

SQL> select dump('node3',16) from dual;

DUMP('NODE3',16)

--------------------------------------------------------

Typ=96 Len=5: 6e,6f,64,65,33

SQL> select hextostr('6e 6f 64 65 33') from dual;

HEXTOSTR('6E6F646533')

--------------------------------------------------------

node3

SQL> select dump ('C_OBJ#',16) from dual;

DUMP('C_OBJ#',16)

--------------------------------------------------------------

Typ=96 Len=6: 43,5f,4f,42,4a,23

SQL>   select hextostr('43 5f 4f 42 4a 23') from dual;

HEXTOSTR('435F4F424A23')

--------------------------------------------------------------

C_OBJ#