full text

Posted on by


CREATE OR REPLACE PROCEDURE get_blob_full_text(
p_key1 IN VARCHAR2,
p_key2 IN VARCHAR2 DEFAULT NULL
) IS
l_full_text CLOB := EMPTY_CLOB();
l_chunk_content VARCHAR2(32767);
BEGIN
— Enable large buffer for DBMS_OUTPUT
DBMS_OUTPUT.ENABLE(1000000);

FOR rec IN (
    SELECT 
        blob_to_text_range(t.VERSIONS, c.start_byte, c.end_byte) as chunk_content
    FROM CacheIOIStates t
    CROSS JOIN (
        SELECT 
            level as chunk_num,
            (level * 4000 - 3999) as start_byte,
            (level * 4000) as end_byte
        FROM dual 
        CONNECT BY level <= CEIL(
            (SELECT blob_length(VERSIONS) FROM CacheIOIStates WHERE IOIID = p_key1) / 4000
        )
    ) c
    WHERE t.IOIID = p_key1
    ORDER BY c.chunk_num
) LOOP
    IF rec.chunk_content IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE(rec.chunk_content);
    END IF;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLERRM);
END get_blob_full_text;
/



Source link