Skip to Main Content
  • Questions
  • ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion when reading AUD$

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Antoine.

Asked: November 28, 2018 - 1:32 pm UTC

Last updated: December 18, 2018 - 1:57 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hello Tom,
I am trying to read sqltext from AUD$. However, I am skipping the 32K and sql is giving me an ORA Error. ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion(actual 4664, maximum 4000). Sql is :

SELECT TO_CHAR(NTIMESTAMP#,'DD-MM-YYYY HH24:MI:SS.FF3') AS EXTENDED_TIMESTAMP,
SESSIONID,
USERID,
USERHOST,
PROCESS# AS TRANSACTIONID ,
OBJ$CREATOR AS OBJECT_SCHEMA,
OBJ$NAME AS OBJECT_NAME,
CAST(SQLTEXT AS VARCHAR2(1000)) SQLTEXT
FROM sys.aud$
ORDER BY 1;

Thanking you in anticipation
Best Regs
Antoine



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.

Thanks a lot
Antoine

and Connor said...

"Pass to internal audit" is the key here - in terms of what you are using to pass it.

If you can pass a clob, that is the best way, ie no need to cast the sqltext column - you simply pass it.

If you *cannot* pass clobs and can only pass varchar2, then you'd probably need to segment the column up into multiple pieces and pass them individually, eg

(I'm using V$SQL in lieu of sys.aud$ here)

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


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks it worked !!

Antoine Bonello, December 17, 2018 - 1:44 pm UTC

Thanks a lot Tom!! Managed to convince IT Security to have the data split into different columns and solution is perfect.

Best Regards,
Antoine
Connor McDonald
December 18, 2018 - 1:57 am UTC

Glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library