Oracle transforms from function to system

hexadecimal conversion to decimal: To_number ()

Sql> Select To_number (' A ', ' X ') from dual; To_number (' A ', ' X ')------------------10

Decimal conversion to 16 binary: To_char ()

Sql> Select To_char (ten, ' xxx ') from dual; To_char (, '------------a

Binary no way direct conversions can be achieved through functions:

Decimal into binary

CREATE OR REPLACE FUNCTION number_to_bit (v_num number) return varchar is V_RTN varchar (8);--Note the return column length v_n1 number; V_N2 number;    BEGINV_N1: = V_num;      LOOP v_n2: = MOD (V_N1, 2); V_N1: = ABS (TRUNC (V_N1/2)); --the absolute value of the V_RTN: = To_char (v_n2) | |      V_rtn;    EXIT when v_n1 = 0; END loop;--return binary length SELECT lpad (v_rtn,8,0)-eight bit less than 0 into V_rtn from Dual;return v_rtn;end;

Binary Conversion to decimal:

Create or replace function bit_to_number (P_BIN IN VARCHAR2)  RETURN  Number as  v_sql varchar2 (30000)  :=  ' select bin_to_num (';   v_ return number; Begin  if length (P_bin)  >= 256 then    raise_application_ ERROR ( -20001,  ' input bin too long! ');   end if;  if ltrim (p_bin,  ')  IS NOT NULL THEN     raise_application_error ( -20002,  ' Input str is not valid bin  value! ');   END IF;  FOR I IN 1&NBSP, ....  length (p_bin)  loop    v_sql := v_sql | |  substr (p_bin, i, 1)  | |   ', ';   end loop;  v_sql := rtrim (v_sql,  ', ')  | |   ')  from dual ';   execute immediate v_sql    INTO V_RETURN;  RETURN V_RETURN; END;

Create a function synonym

Create public synonym Number_to_bit for number_to_bit;grant execute in number_to_bit to public

16 to two and two to 16, you can combine the 10 turn 16 function and the two-turn 10 function, which is not an additional example.

ASCII code and Oracle metadata conversion functions: Rawtohex and Hextoraw

Sql> Select Rawtohex (1) from dual; Rawtohex (1)------------c102sql> Select Rawtohex (' 1 ') from dual; Rawtoh------31sql> Select Rawtohex (' a ') from dual; Rawtoh------61sql> Select Rawtohex (' A ') from dual; Rawtoh------41sql> Select Hextoraw (' A ') from dual; He--0asql> Select Hextoraw (' 1 ') from dual; He--01

This article is from the "linuxoracle" blog, make sure to keep this source http://onlinekof2001.blog.51cto.com/3106724/1652746

Oracle translates into functions through a function