No1see,
I made the below PL/SQL function to decode the Text in F00165 into a VARCHAR2 variable (limit 4000 characters). Function is typically used as part of a View query (see sample below). Text will still be in Rich text Format however. If you don't want RTF then you'll need to google for ways to parse that out.
----------------------------------
CREATE OR REPLACE FUNCTION "PRODDTA"."JDETEXTFIX" (p_rowid in
ROWID) RETURN VARCHAR2
IS
v_textout VARCHAR2(4000);
v_lob BLOB;
v_buffer RAW(8192);
v_offset INTEGER := 1;
v_textlen BINARY_INTEGER := 8192;
v_rawlen NUMBER;
v_from_set RAW(2);
v_to_set RAW(1);
v_output RAW(8192);
BEGIN
SELECT gdtxft INTO v_lob
FROM F00165 where ROWID = p_rowid;
DBMS_LOB.READ (v_lob, v_textlen, v_offset,v_buffer);
v_textout := ' ';
v_from_set := HEXTORAW('7D00');
v_to_set := HEXTORAW('7D');
v_output := UTL_RAW.TRANSLATE (v_buffer, v_from_set, v_to_set);
v_rawlen := UTL_RAW.LENGTH (v_output);
if (v_rawlen > 4000) then
v_rawlen := 4000;
end if;
if (v_rawlen > 0) then
v_textout := UTL_RAW.CAST_TO_VARCHAR2 (UTL_RAW.SUBSTR(v_output,1,v_rawlen));
end if;
RETURN (v_textout);
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN (v_textout);
END;
------------------------------------------------------------------
CREATE OR REPLACE VIEW "PRODDTA"."F00165_VIEW"
("NAMEOBJECT_GDOBNM","GENERICTEXTKEY_GDTXKY","SEQNO_GDMOSEQN",
"MEDIAOBJTYPE_GDGTMOTYPE","LANGUAGEPREFERENCE_GDLNGP",
"UPDATEDBYUSER_GDUSER","DATEUPDATED_GDUPMJ",
"TIMEOFDAY_GDTDAY","OBJECTTYPENAME_GDGTITNM",
"QUEUENAME_GDQUNAM","FILENAME_GDGTFILENM",
"MEDIAOBJECTVARIABLELENG_GDTXVC") AS
SELECT
GDOBNM,
GDTXKY,
GDMOSEQN,
GDGTMOTYPE,
GDLNGP,
GDUSER,
JDE2Date(GDUPMJ),
GDTDAY,
GDGTITNM,
GDQUNAM,
GDGTFILENM,
JDETextFix(ROWID)
FROM F00165
WHERE GDGTMOTYPE = 0 WITH READ ONLY
------------------------------------------------------------------