DECLARE
|
FUNCTION blob2clob (i_blob IN BLOB) RETURN CLOB AS
|
o_clob CLOB;
|
i_dest_offset PLS_INTEGER := 1;
|
i_src_offset PLS_INTEGER := 1;
|
i_lang_context PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
|
i_Warning PLS_INTEGER;
|
BEGIN
|
IF i_blob IS NOT NULL THEN
|
IF LENGTH(i_blob) = 0 THEN
|
RETURN EMPTY_CLOB();
|
END IF;
|
DBMS_LOB.createTemporary(lob_loc => o_clob,
|
cache => TRUE); -- read into buffer cache
|
DBMS_LOB.CONVERTTOCLOB(
|
dest_lob => o_clob,
|
src_blob => i_blob,
|
amount => DBMS_LOB.LOBMAXSIZE,
|
dest_offset => i_dest_Offset,
|
src_offset => i_src_Offset,
|
blob_csid => DBMS_LOB.DEFAULT_CSID,
|
lang_context => i_lang_context,
|
warning => i_Warning
|
);
|
ELSE
|
o_clob := NULL;
|
END IF;
|
RETURN o_Clob;
|
EXCEPTION
|
WHEN OTHERS THEN RAISE;
|
END blob2clob;
|
BEGIN
|
FOR txt IN (SELECT architecture_desc arch_desc FROM lu_db_architecture) LOOP
|
DBMS_OUTPUT.PUT_LINE(CHR(10)||' -> '||SUBSTR(blob2clob(txt.arch_desc),1,250));
|
DBMS_OUTPUT.PUT_LINE(CHR(10)||' -> '||SUBSTR(blob2clob(txt.arch_desc),251));
|
END LOOP;
|
END;
|
/
|