ORACLE decimal and binary interchange functions

Decimal conversion Binary

The code is as follows

Copy Code

CREATE OR REPLACE FUNCTION number_to_bit (v_num number) Return VARCHAR is V_rtn VARCHAR (8);--note returns the column length V_N1 number; V_N2 number; BEGIN V_N1: = V_num; LOOP V_N2: = MOD (V_N1, 2); V_N1: = ABS (TRUNC (V_N1/2)); V_rtn: = To_char (v_n2) | | V_rtn; EXIT when v_n1 = 0; End LOOP; --Returns the binary length SELECT Lpad (v_rtn,8,0) Into V_rtn from dual; return V_RTN; End Sql> Select Number_to_bit (a) from dual; Number_to_bit (208) ----------------------------- 11010000

Binary Conversion Decimal

The code is as follows

Copy Code

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. 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; sql> SELECT bit_to_number (' 11010000 ') from DUAL; Bit_to_number (' 11010000 ')

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

Binary conversion octal

The code is as follows

Copy Code

FUNCTION f_bin_to_oct (p_str in VARCHAR2) return VARCHAR2 is --------------------------------------------------------------------------------------------------------------- ------- --Object name: F_bin_to_oct --Object Description: Binary conversion octal --Input parameter: P_str binary string --Returns the result: octal string --Test Case: SELECT pkg_number_trans.f_bin_to_oct (' 11110001010 ') from dual; --Note: You need to define F_STRAGG functions and Type_str_agg types --------------------------------------------------------------------------------------------------------------- ------- V_return VARCHAR2 (4000); V_bin VARCHAR2 (4000); BEGIN V_bin: = substr (' n ' | | p_str,-3 * ceil (Length (P_STR)/3)); SELECT F_stragg (data1) into V_return From (case Upper (substr (V_bin, (rownum-1) * 3 + 1, 3)) When ' THEN ' 0 ' When ' 001 ' THEN ' 1 ' When ' 010 ' THEN ' 2 ' When ' 011 ' THEN ' 3 ' When ' THEN ' 4 ' When ' THEN ' 5 ' When ' THEN ' 6 ' When ' THEN ' 7 ' End) Data1 From dual CONNECT by rownum <= Length (v_bin)/3); return v_return; EXCEPTION When others THEN return NULL; End F_bin_to_oct;