Error message ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion is reported when attempting to configure Oracle ODBC collection in RSA Security Analytics

Issue

When configuring ODBC connection for Oracle 10g or 11g databases using the default template with extended logging enabled, an error similar to the example below is observed.

[OdbcCollection] [failure] [oracle_11g_auditing.dbanfs] [processing] [dbanfs] [processing] Data query failed; dataQuery: SELECT "SYS"."V_$INSTANCE"."VERSION", '-', "SYS"."V_$INSTANCE"."HOST_NAME", '-', "SYS"."V_$INSTANCE"."INSTANCE_NAME", '-', '-', "SYS"."AUD$"."SESSIONID", "SYS"."AUD$"."ENTRYID", "SYS"."AUD$"."STATEMENT", CASE "SYS"."AUD$"."ACTION#" WHEN 101 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') WHEN 102 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') ELSE TO_CHAR(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date),'yyyy-mm-dd hh24:mi:ss') END "timestamp", "SYS"."AUD$"."USERID", "SYS"."AUD$"."USERHOST", "SYS"."AUD$"."TERMINAL", "SYS"."AUD$"."ACTION#", "SYS"."AUD$"."RETURNCODE", "SYS"."AUD$"."OBJ$CREATOR", "SYS"."AUD$"."OBJ$NAME", "SYS"."AUD$"."AUTH$PRIVILEGES", "SYS"."AUD$"."AUTH$GRANTEE", "SYS"."AUD$"."NEW$OWNER", "SYS"."AUD$"."NEW$NAME", "SYS"."AUD$"."SES$ACTIONS", "SYS"."AUD$"."SES$TID", "SYS"."AUD$"."LOGOFF$LREAD", "SYS"."AUD$"."LOGOFF$PREAD", "SYS"."AUD$"."LOGOFF$LWRITE", "SYS"."AUD$"."LOGOFF$DEAD", COALESCE(TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss'),' ') as LOGOFF$TIME, "SYS"."AUD$"."COMMENT$TEXT", "SYS"."AUD$"."CLIENTID", "SYS"."AUD$"."SPARE1", "SYS"."AUD$"."SPARE2", "SYS"."AUD$"."OBJ$LABEL", "SYS"."AUD$"."SES$LABEL", "SYS"."AUD$"."PRIV$USED", "SYS"."AUD$"."SESSIONCPU" , cast("SYS"."AUD$"."SQLBIND" as varchar(2000)) as SQL_BIND, cast("SYS"."AUD$"."SQLTEXT" as varchar(2000)) as SQL_TEXT FROM "SYS"."AUD$", "SYS"."V_$INSTANCE" WHERE (cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) > to_date('2015-11-09 14:41:12','yyyy-mm-dd hh24:mi:ss') AND SYSDATE-(2/(3600*24)g] Error finding any new events. Reason: SQLFetch failed: Statement: "SELECT "SYS"."V_$INSTANCE"."VERSION", '-', "SYS"."V_$INSTANCE"."HOST_NAME", '-', "SYS"."V_$INSTANCE"."INSTANCE_NAME", '-', '-', "SYS"."AUD$"."SESSIONID", "SYS"."AUD$"."ENTRYID", "SYS"."AUD$"."STATEMENT", CASE "SYS"."AUD$"."ACTION#" WHEN 101 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') WHEN 102 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') ELSE TO_CHAR(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date),'yyyy-mm-dd hh24:mi:ss') END "timestamp", "SYS"."AUD$"."USERID", "SYS"."AUD$"."USERHOST", "SYS"."AUD$"."TERMINAL", "SYS"."AUD$"."ACTION#", "SYS"."AUD$"."RETURNCODE", "SYS"."AUD$"."OBJ$CREATOR", "SYS"."AUD$"."OBJ$NAME", "SYS"."AUD$"."AUTH$PRIVILEGES", "SYS"."AUD$"."AUTH$GRANTEE", "SYS"."AUD$"."NEW$OWNER", "SYS"."AUD$"."NEW$NAME", "SYS"."AUD$"."SES$ACTIONS", "SYS"."AUD$"."SES$TID", "SYS"."AUD$"."LOGOFF$LREAD", "SYS"."AUD$"."LOGOFF$PREAD", "SYS"."AUD$"."LOGOFF$LWRITE", "SYS"."AUD$"."LOGOFF$DEAD", COALESCE(TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss'),' ') as LOGOFF$TIME, "SYS"."AUD$"."COMMENT$TEXT", "SYS"."AUD$"."CLIENTID", "SYS"."AUD$"."SPARE1", "SYS"."AUD$"."SPARE2", "SYS"."AUD$"."OBJ$LABEL", "SYS"."AUD$"."SES$LABEL", "SYS"."AUD$"."PRIV$USED", "SYS"."AUD$"."SESSIONCPU" , cast("SYS"."AUD$"."SQLBIND" as varchar(2000)) as SQL_BIND, cast("SYS"."AUD$"."SQLTEXT" as varchar(2000)) as SQL_TEXT FROM "SYS"."AUD$", "SYS"."V_$INSTANCE" WHERE (cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) > to_date('2015-11-09 14:41:12','yyyy-mm-dd hh24:mi:ss') AND SYSDATE-(2/(3600*24)) > cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) AND "SYS"."AUD$"."ACTION#" NOT IN (101,102)) OR ("SYS"."AUD$"."LOGOFF$TIME" > to_date('2015-11-09 14:41:12','yyyy-mm-dd hh24:mi:ss') AND "SYS"."AUD$"."ACTION#" IN (101,102) AND SYSDATE-(2/(3600*24)) > "SYS"."AUD$"."LOGOFF$TIME" ) ORDER BY 11 ASC"; Reason: state: S1000; error-code: 22835; description: [RSA][ODBC Oracle Wire Protocol driver][Oracle]ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 25318, maximum: 4000)

Cause

The error means the SQL query is selecting from an Oracle LOB (Large OBjects) data type (CLOB which has a maximum size of 4GB-1), and the data returned is too long to fit into the target data type, VARCHAR2 (maximum size 4,000 bytes).

CAST does not directly support any of the LOB datatypes. When using CAST to convert a CLOB value into a character datatype, the database implicitly converts the LOB value to characters, and then explicitly casts the resulting value into the target datatype. If the resulting value is larger than the target type, then the database returns an error.

Workaround

To resolve the issue, download the XML files attached to this article, and follow the steps below.

1. SSH to the Log Collector appliance as the root user, and stop the nwlogcollector service.

 stop nwlogcollector

2. On the Log Collector, navigate to the /etc/netwitness/ng/logcollection/content/collection/odbc directory, and create a backup of the oracle_10g_auditing.xml and oracle_11g_auditing.xml files.

 cp -p /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_10g_auditing.xml /root/oracle_10g_auditing.xml.bkp
 
rm -rf /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_10g_auditing.xml
 
cp -p /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_11g_auditing.xml /root/oracle_11g_auditing.xml.bkp
 
rm -rf /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_11g_auditing.xml

3. Replace these files with those attached to this article.

Warning: Don't keep a backup of the .xml files in the /etc/netwitness/ng/logcollection/content/collection/odbc/ directory, SA will use the first oracle_10g_auditing*, and the first oracle_11g_auditing* file it finds in this directory.

4. Start the nwlogcollector service again.

 start nwlogcollector

5. Check whether or not the ODBC collection has started.

6. Check whether or not ODBC logs are being collected for the given event sources.

Addendum:
After making the above change there may occur an error relating to :basic_string::_S_create

Product Details

RSA Product Set: NetWitness Logs and Packets (Security Analytics)
RSA Product/Service Type: Log Collector
RSA Version/Condition: 10.5.x, 10.6.x
Platform: CentOS
Platform (Other): Oracle 10g, 11g
O/S Version: EL6

Summary

Security Analytics ODBC Oracle collection failure on Oracle 11g with error [ORA-22835].

Approval Reviewer Queue

KCS Approval queue