oracle clob raw 转换,LONG RAW to CLOB conversion --- LONG RAW exceeding 32K SIZE for some col. — orac...

A Example, the field CONTENTHTML is LOW RAW.

CREATE OR REPLACE PROCEDURE Replace_Text_From_BLOB (

numReg IN INTEGER,

replaceStr IN VARCHAR2,

replaceWith IN VARCHAR2) IS

vBuffer VARCHAR2 (32767);

l_amount BINARY_INTEGER := 32767;

l_pos PLS_INTEGER := 1;

l_clob_len PLS_INTEGER;

newClob CLOB := EMPTY_CLOB;

srcClob CLOB;

vBuffer1 VARCHAR2 (32767);

cont PLS_INTEGER := 1;

pos PLS_INTEGER := 1;

buffer LONG RAW( 32767 );

CURSOR c_tempBlob IS

SELECT CONTENTCODE, NEWSCODE, CONTENTTYPECODE,

CONTENTCREATIONDATE, LOBCOL, CONTENTRSSURL,

CONTENTPAGENUMBER

FROM ADMCMS.TEMP_BLOB ORDER BY CONTENTCODE;

BEGIN

DELETE ADMCMS.TEMP_T_CONTENT;

DELETE ADMCMS.TEMP_BLOB;

INSERT INTO TEMP_BLOB SELECT CONTENTCODE, NEWSCODE, CONTENTTYPECODE,CONTENTCREATIONDATE,TO_LOB( CONTENTHTML) LOBCOL ,CONTENTRSSURL,

CONTENTPAGENUMBER FROM T_CONTENT;

FOR tmp IN c_tempBlob LOOP

-- initalize the new clob

newClob:=null;

dbms_lob.createtemporary(newClob,TRUE);

l_clob_len := dbms_lob.getlength(tmp.LOBCOL);

vBuffer1 :=UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(tmp.LOBCOL, l_clob_len, 1));

srcClob := TO_CLOB(vBuffer1);

l_pos := 1;

--Recorre el texto y lo modifica

WHILE l_pos < l_clob_len

LOOP

dbms_lob.read(srcClob, l_amount, l_pos, vBuffer);

IF vBuffer IS NOT NULL THEN

-- reemplaza el texto

vBuffer := replace(vBuffer, replaceStr, replaceWith);

-- escribe al new clob

dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);

END IF;

l_pos := l_pos + l_amount;

END LOOP;

buffer := UTL_RAW.CAST_TO_RAW (newClob);

INSERT INTO ADMCMS.TEMP_T_CONTENT (

CONTENTCODE, NEWSCODE, CONTENTTYPECODE,

CONTENTCREATIONDATE, CONTENTHTML, CONTENTRSSURL,

CONTENTPAGENUMBER)

VALUES (tmp.CONTENTCODE ,tmp.NEWSCODE ,tmp.CONTENTTYPECODE ,tmp.CONTENTCREATIONDATE

, buffer ,tmp.CONTENTRSSURL ,tmp.CONTENTPAGENUMBER );

COMMIT;

cont := cont + 1;

IF cont> numReg THEN

EXIT;

END IF;

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN

NULL;

WHEN OTHERS THEN

-- Consider logging the error and then re-raise

RAISE;

END Replace_Text_From_BLOB;

/