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