-- convert from hex to dec only using datatypes number and varchar2
CREATE OR REPLACE PROCEDURE convert_to_dec(p_input IN VARCHAR2)
IS
  l_str VARCHAR2(12);
  l_val NUMBER;
  l_bin VARCHAR2(4000) := ' ';
  e_invalid_input EXCEPTION;
BEGIN
  l_str := LOWER(p_input);
  FOR i IN 1..LENGTH(l_str)
  LOOP
    l_val := ASCII(SUBSTR(l_str,i,1))-87;

    IF l_val BETWEEN 10 AND 15 THEN

        IF l_val - 8 >= 0 THEN
           l_val := l_val - 8;
           l_bin := l_bin||'1';
        ELSE
           l_bin := l_bin||'0';
        END IF;

        IF l_val - 4 >= 0 THEN
           l_val := l_val - 4;
           l_bin := l_bin||'1';
        ELSE
           l_bin := l_bin||'0';
        END IF;

        IF l_val - 2 >= 0 THEN
           l_val := l_val - 2;
           l_bin := l_bin||'1';
        ELSE
           l_bin := l_bin||'0';
        END IF;

        IF l_val - 1 >= 0 THEN
           l_val := l_val - 1;
           l_bin := l_bin||'1';
        ELSE
           l_bin := l_bin||'0';
        END IF;

    ELSE
      RAISE e_invalid_input;
    END IF;
  END LOOP;

  l_bin := LTRIM(l_bin);

  l_val := 0;

  FOR i IN 1..LENGTH(l_bin)-1
  LOOP
    IF SUBSTR(l_bin,LENGTH(l_bin)-i,1) = '1' THEN
      l_val := l_val + POWER(2,i);
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line(l_val);
EXCEPTION
  WHEN e_invalid_input THEN
    DBMS_OUTPUT.put_line('"'||l_str||'" contains characters that are not a,b,c,d,e,f');
END;