SQL> CREATE OR REPLACE
  2    PACKAGE HEX
  3      IS
  4        FUNCTION TO_BIT(
  5                        P_HEX VARCHAR2
  6                       )
  7      RETURN VARCHAR2;
  8  END;
  9  /

Package created.

SQL> CREATE OR REPLACE
  2    PACKAGE BODY HEX
  3      IS
  4        TYPE TETRADE_TYPE
  5            IS
  6              TABLE OF VARCHAR2(4)
  7                INDEX BY VARCHAR2(1);
  8        G_TETRADES TETRADE_TYPE;
  9        FUNCTION TO_BIT(
 10                        P_HEX VARCHAR2
 11                       )
 12          RETURN VARCHAR2
 13          IS
 14          BEGIN
 15              IF LENGTH(P_HEX) IS NULL
 16                THEN
 17                  RETURN NULL;
 18                ELSE
 19                  RETURN G_TETRADES(SUBSTR(P_HEX,1,1)) || TO_BIT(SUBSTR(P_HEX,2));
 20              END IF;
 21        END;
 22      BEGIN
 23          G_TETRADES('0') := '0000';
 24          G_TETRADES('1') := '0001';
 25          G_TETRADES('2') := '0010';
 26          G_TETRADES('3') := '0011';
 27          G_TETRADES('4') := '0100';
 28          G_TETRADES('5') := '0101';
 29          G_TETRADES('6') := '0110';
 30          G_TETRADES('7') := '0111';
 31          G_TETRADES('8') := '1000';
 32          G_TETRADES('9') := '1001';
 33          G_TETRADES('A') := '1010';
 34          G_TETRADES('B') := '1011';
 35          G_TETRADES('C') := '1100';
 36          G_TETRADES('D') := '1101';
 37          G_TETRADES('E') := '1110';
 38          G_TETRADES('F') := '1111';
 39          G_TETRADES('a') := '1010';
 40          G_TETRADES('b') := '1011';
 41          G_TETRADES('c') := '1100';
 42          G_TETRADES('d') := '1101';
 43          G_TETRADES('e') := '1110';
 44          G_TETRADES('f') := '1111';
 45          G_TETRADES(' ') := NULL;
 46  END;
 47  /

Package body created.

SQL> COLUMN BITS FORMAT A16
SQL> SELECT  65533 DEC,
  2          TO_CHAR(65533,'XXXX') HEX,
  3          HEX.TO_BIT(TO_CHAR(65533,'XXXX')) BITS
  4    FROM  DUAL
  5  /

       DEC HEX   BITS
---------- ----- ----------------
     65533  FFFD 1111111111111101

SQL> 

SQL> with data as (select 'FFFD' in_val from dual union all select '0FFF' from dual)
  2  select in_val,
  3         case when to_number(in_val,'XXXX') < 32768 then to_number(in_val,'XXXX')
  4              else to_number(in_val,'XXXX')-65536
  5         end out_val
  6  from data
  7  /
IN_V    OUT_VAL
---- ----------
FFFD         -3
0FFF       4095