Carlos Sierra's Tools and Tips

If you have looked at the values on DBA_TAB_HISTOGRAMS.ENDPOINT_VALUE you may have seen some like 281422806648623000000000000000000000 when you were actually expecting something like 63736. I have used in SQLT the two functions below for many years. I got the foundations of these two out of a public source which unfortunately I did not record. Since you may or not may have SQLT, I thought a good idea to share these two functions in case you need them.

CREATE OR REPLACE
FUNCTION get_internal_value (p_value IN VARCHAR2)
RETURN VARCHAR2
IS
 temp_n NUMBER := 0;
BEGIN
 FOR i IN 1..15
 LOOP
 temp_n := temp_n + POWER(256, 15 - i) * ASCII(SUBSTR(RPAD(p_value, 15, CHR(0)), i, 1));
 END LOOP;
 RETURN TO_CHAR(ROUND(temp_n, -21));
EXCEPTION
 WHEN OTHERS THEN
 RETURN p_value;
END get_internal_value;
/

CREATE OR REPLACE
FUNCTION get_external_value (p_value IN VARCHAR2)
RETURN VARCHAR2
IS
 temp_n NUMBER;
 temp_i INTEGER;
 my_result VARCHAR2(32767) := NULL;

BEGIN
 IF LENGTH(p_value) != 36 OR
 SUBSTR(p_value, 16) != '000000000000000000000' OR
 p_value > POWER(256, 15) OR
 p_value < POWER(256, 14) THEN
 RETURN p_value; -- cannot get external value
 END IF;

temp_n := p_value / POWER(256, 14); -- get most significant digits

-- decoding most significant digits then shift multiplying by 256
 FOR i IN 1..14
 LOOP
 temp_i := TRUNC(temp_n);
 temp_n := (temp_n - temp_i) * 256;
 IF temp_i NOT BETWEEN 32 AND 126 OR temp_n NOT BETWEEN 32 AND 126 THEN
 EXIT; -- reached the tail
 END IF;
 my_result := my_result||CHR(temp_i); -- all but last byte
 END LOOP;

IF temp_i NOT BETWEEN 32 AND 126 THEN
 RETURN my_result||'?'; -- cannot decode
 END IF;

-- scan to the right starting at temp_i
 FOR i IN temp_i..126
 LOOP
 IF get_internal_value(my_result||CHR(i)) = p_value THEN
 RETURN my_result||CHR(i); -- approximate value
 END IF;
 END LOOP;

-- scan to the left starting at temp_i
 FOR i IN 32..temp_i
 LOOP
 IF get_internal_value(my_result||CHR(temp_i + 32 - i)) = p_value THEN
 RETURN my_result||CHR(temp_i + 32 - i); -- approximate value
 END IF;
 END LOOP;

RETURN my_result||CHR(temp_i); -- this is the best we could do
EXCEPTION
 WHEN OTHERS THEN
 RETURN p_value;
END get_external_value;
/

SELECT get_external_value('281422806648623000000000000000000000')
FROM DUAL;

GET_EXTERNAL_VALUE('281422806648623000000000000000000000')
----------------------------------------------------------
63736

SELECT get_internal_value('63736') FROM DUAL;

GET_INTERNAL_VALUE('63736')
----------------------------------------------------------
281422806648623000000000000000000000