Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (value TIMESTAMP WITH TIME ZONE);
INSERT INTO table_name (value) VALUES (TIMESTAMP '2018-03-27 12:34:56.007890000 AMERICA/NEW_YORK');
1 rows affected

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR';
SELECT value, DUMP(value,16) FROM table_name


VALUE
DUMP(VALUE,16)


2018-03-27 12:34:56.007890 AMERICA/NEW_YORK
Typ=181 Len=13: 78,76,3,1b,11,23,39,0,78,64,50,81,90


CREATE AND COMPILE JAVA SOURCE NAMED HexToTimestampConverter AS
import oracle.sql.TIMESTAMP;
import oracle.sql.TIMESTAMPTZ;

public class HexToTimestampConverter {
  public static byte[] hexStringToByteArray(String s) {
    int len = s.length();
    byte[] data = new byte[len / 2];
    for (int i = 0; i < len; i += 2) {
      data[i / 2] = (byte) ((Character.digit(s.charAt(i), 16) << 4)
                           + Character.digit(s.charAt(i+1), 16));
    }
    return data;
  }

  public static TIMESTAMP hexToTimestamp(final String hex)
  {
    return new TIMESTAMP(hexStringToByteArray(hex));
  }

  public static TIMESTAMPTZ hexToTimestamptz(final String hex)
  {
    return new TIMESTAMPTZ(hexStringToByteArray(hex));
  }
}
CREATE FUNCTION hex_to_timestamp( in_value IN VARCHAR2 ) RETURN TIMESTAMP
AS LANGUAGE JAVA NAME 'HexToTimestampConverter.hexToTimestamp(java.lang.String) return oracle.sql.TIMESTAMP';
/
CREATE FUNCTION hex_to_timestamptz( in_value IN VARCHAR2 ) RETURN TIMESTAMP WITH TIME ZONE
AS LANGUAGE JAVA NAME 'HexToTimestampConverter.hexToTimestamptz(java.lang.String) return oracle.sql.TIMESTAMPTZ';
/
SELECT hex_to_timestamp(hex_value) AS timestamp_val,
       hex_value
FROM   (
  SELECT '7876031b0d233900786450' AS hex_value FROM DUAL
)


TIMESTAMP_VAL
HEX_VALUE


2018-03-27 12:34:56.007890000
7876031b0d233900786450


SELECT hex_to_timestamptz(hex_value) AS timestamp_val,
       hex_value
FROM   (
  SELECT '7876031b0d2339007864508190' AS hex_value FROM DUAL
)


TIMESTAMP_VAL
HEX_VALUE


2018-03-27 08:34:56.007890000 AMERICA/NEW_YORK
7876031b0d2339007864508190