Oracle 18c

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

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


VALUE
DUMP(VALUE,16)


2018-03-27 12:34:56.007890000
Typ=180 Len=11: 78,76,3,1b,d,23,39,0,78,64,50


SELECT CAST(
         DBMS_STATS.CONVERT_RAW_TO_DATE(HEXTORAW(SUBSTR(hex_value,1,14)))
         AS TIMESTAMP(9)
       )
       + INTERVAL '1' SECOND(9)
         * TO_NUMBER(HEXTORAW(SUBSTR(hex_value,15,8)), 'XXXXXXXX') / 1e9
         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


CREATE FUNCTION hex_to_timestamp(
  hex_value IN VARCHAR2
) RETURN TIMESTAMP DETERMINISTIC
IS
BEGIN
  RETURN CAST(
           DBMS_STATS.CONVERT_RAW_TO_DATE(HEXTORAW(SUBSTR(hex_value,1,14)))
           AS TIMESTAMP
         )
         + INTERVAL '1' SECOND
           * TO_NUMBER(HEXTORAW(SUBSTR(hex_value,15,8)), 'XXXXXXXX') / 1e9;
END;
/
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 * FROM USER_ERRORS;