Someone posted this earlier, but here it is. Create the function before you create the view.
Part I: the view
CREATE OR REPLACE VIEW PRODDTA.F00165_VIEW
(NAMEOBJECT_GDOBNM, GENERICTEXTKEY_GDTXKY, SEQNO_GDMOSEQN, MEDIAOBJTYPE_GDGTMOTYPE, LANGUAGEPREFERENCE_GDLNGP,
UPDATEDBYUSER_GDUSER, TIMEOFDAY_GDTDAY, OBJECTTYPENAME_GDGTITNM, QUEUENAME_GDQUNAM, FILENAME_GDGTFILENM,
MEDIAOBJECTTRIMMED)
AS
SELECT
GDOBNM,
GDTXKY,
GDMOSEQN,
GDGTMOTYPE,
GDLNGP,
GDUSER,
GDTDAY,
GDGTITNM,
GDQUNAM,
GDGTFILENM,
substr(substr(JDETextFix(ROWID),156,length(JDETextFix(ROWID))),0,instr(substr(JDETextFix(ROWID),156,length(JDETextFix(ROWID))),'\')-1)
FROM F00165
WHERE GDGTMOTYPE = 0;
Part two: The function jdetextfix
CREATE OR REPLACE FUNCTION 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;
/
This won't be fast, since its a blob, but it does work.
Morglum