create or replace FUNCTION hextodec(a IN VARCHAR2) RETURN NUMBER IS 
  x NUMBER := 0; 
  y NUMBER; 
  z VARCHAR2(1);  
BEGIN 
  FOR i IN 1..length(a) LOOP      
    z := upper(substr(a,i,1));     
    IF z = 'A' THEN
      y := 10;       
    ELSIF z = 'B' THEN     
      y := 11;     
    ELSIF z = 'C' THEN     
      y := 12;     
    ELSIF z = 'D' THEN     
      y := 13;     
    ELSIF z = 'E' THEN     
      y := 14;     
    ELSIF z = 'F' THEN     
      y := 15;     
    ELSE     
      y := to_number(z,9);     
    END IF;      
    x := x * 16;     
    x := x + y;     
  END LOOP;     
  RETURN x;     
END hextodec; 
/
