Oracle SQL to read a BLOB

Oracle SQL to read a BLOB

CREATE OR REPLACE PROCEDURE read_blob
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

SELECT PZPVSTREAM INTO l_blob FROM PEGARULES.XCS_WORK
WHERE pxinsname = 'W-10332';

-- 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;
/