So does your BLOB contain textual information?
If so, this procedure will let you at least see it. One drawback of the procedure is that the formatting gets broken up due to DBMS_OUTPUT and VARCHAR2 length limitations.CREATE OR REPLACE PROCEDURE read_blob (p_fnd_lobs_name IN VARCHAR2)
IS
l_blob BLOB;
l_blob_length NUMBER;
l_amount BINARY_INTEGER := 10000; -- must be <= ~32765.
l_offset INTEGER := 1;
l_buffer RAW(20000);
l_text_buffer VARCHAR2(32767);
l_break_at PLS_INTEGER := 141; -- must be <= 255.
BEGIN
-- Get the BLOB from the APPLSYS.FND_LOBS table.
SELECT fl.file_data
INTO l_blob
FROM applsys.fnd_lobs fl
WHERE fl.file_name = p_fnd_lobs_name;
-- Figure out how long the BLOB is.
l_blob_length := DBMS_LOB.GETLENGTH(l_blob);
-- We'll loop through the BLOB as many times as necessary to
-- get all its data.
FOR i IN 1..CEIL(l_blob_length/l_amount) LOOP
-- Read in the given chunk of the BLOB.
DBMS_LOB.READ(l_blob
, l_amount
, l_offset
, l_buffer);
-- The DBMS_LOB.READ procedure dictates that its output be RAW.
-- This next procedure converts that RAW data to character data.
l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer);
-- Now that we have character data (up to l_amount characters long),
-- chunk it out so that we can call DBMS_OUTPUT (which only accepts
-- arguments less than or equal to 255 characters).
FOR j IN 1..CEIL(LENGTH(l_text_buffer) / l_break_at) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(l_text_buffer
, (((j - 1) * l_break_at) + 1)
, LEAST(LENGTH(l_text_buffer)
-
((j - 1) * l_break_at)
, l_break_at)));
END LOOP;
-- For the next iteration through the BLOB, bump up your offset
-- location (i.e., where you start reading from).
l_offset := l_offset + l_amount;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('!ERROR: ' || SUBSTR(SQLERRM,1,247));
END;
/
How you get to your BLOB will vary by application; the above is an Oracle Apps solution.
Hope this helps,
Art.