connect by

 SELECT SUM(DATA) --INTOv_return
      FROM(SELECT (CASE upper(substr(:p_str, rownum, 1))
                     WHEN'A' THEN '10'
                     WHEN'B' THEN '11'
                     WHEN'C' THEN '12'
                     WHEN'D' THEN '13'
                     WHEN'E' THEN '14'
                     WHEN'F' THEN '15'
                     ELSE substr(:p_str, rownum, 1)
                   END) * power(16, length(:p_str) - rownum) DATA
              FROM dual
            CONNECT BY rownum <= length(:p_str));

16進制轉換為10進制