• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Blob fields in database.

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
 

altquark

Member
Yes and no.

There are some BLOB readers out there (I think Toad can view Blobs if I recall) - but without the actual knowledge of how the blob field is being handled, all you're going to see is gobblygook.

What are you trying to achieve ? Can you perform the lookup using the UTB ?
 

Larry_Jones

Member
The referenced view and function was my contribution - and it was specific to the text blob in F00165. If the OP is looking for something else - the referenced function won't work.

Regards,
 
At one client we used the PL\SQL Developer - a truly awesome kit of kit.

In the query results table, against a blob was a ... button, press it and it would display the hex and the ascii, gave us enough interactivley to work out what was going on (excellent for diagnosing MRP generation errors in SBS mode)
 

Carl

Member
I use this:

select *,gdobnm,gdgtitnm,gdtxky,convert(nvarchar(4000),convert(binary(8000),gdtxft))as RTFtext from jde_development.testdta.f00165 where gdobnm='ABGT'

E1=8.9 SP7, Tools=8.97.03, db=SQL Server 2000 SP4, OpSys=Win2003
 

Adam (DF)

Member
Has anyone written this query for AS/400/iSeries yet? We have tried a few things, but DB/2 for iSeries chokes on the conversion to double-byte characters.
 

Larry_Jones

Member
I have a improved version of JDETEXTFIX for SQL Server but basically what it does is this:

cast(cast(F00165.GDTXFT as varbinary(max)) as nvarchar(max))
 
In SQL Developer.

Crtl + Click to table.

Select tab "Data" and edit de blob field with the pencil.
You have one option to download as a file.

You can then edit with notepad and upload again.

Thanks
 
Top