Blob fields in database.

FreddyFrancis

FreddyFrancis

Well Known Member
Hello List,

Is there a way I can read the blob fields in database, the database being oracle 10g ?

Regards,
Freddy
 
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
 
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 ?
 
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)
 
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
 
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.
 
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
 
Back
Top