Error - ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion
More Explain:
SYS.AUD$.SQLTEXT which is a CLOB contains max length of 15573 which is way too much to read it in a varchar2. Now, we need to pass data from AUD$ to internal audit and we are encountering a hurdle whenever we are hitting the maximum length a varchar2 can fit in. We need to extract the contents of the CLOB column sqltext as in in whole and pass to internal audit. That is in a nut shell, we need to get data as is out of the sys.aud$ including the whole sqltext column.
varchar2最大字节4000;
解决办法 将CLOB 转换 VARCHAR2 按4000为一组 进行截取
with data as
( select sql_fulltext, dbms_lob.getlength(sql_fulltext) len from v$sql )
select
dbms_lob.substr(sql_fulltext,4000,1) piece1,
case when len > 4000 then dbms_lob.substr(sql_fulltext,4000,4001) end piece2,
case when len > 8000 then dbms_lob.substr(sql_fulltext,4000,8001) end piece3,
case when len > 12000 then dbms_lob.substr(sql_fulltext,4000,12001) end piece4,
case when len > 16000 then dbms_lob.substr(sql_fulltext,4000,165001) end piece5
from data
-- 刘轶鹤