Converting a NCLOB to a VARCHAR2 in TO_DATE after error ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion in RSA Via Lifecycle and Governance

[#1]

Article Number

000033464

Applies To

RSA Product Set: Identity Management and Governance
RSA Version/Condition: 6.8.1 and higher
 

Issue

A workflow query receives the following error:
 

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4065, maximum: 4000)"

The workflow may be stuck at that node.

 

Resolution

The error means that we are selecting from an Oracle LOB (Large OBjects) data type (CLOB, BLOB,NCLOB), and the data is too long to fit into the target data type, which may be RAW or VARCHAR2.

The query in this particular case contained a select like this:

SELECT DISTINCT to_char(wp_work_item_hist.optional_comments)

The column OPTIONAL_COMMENTS in the WP_WORK_ITEM_HIST table is set as an NCLOB data type, which can hold up to 4 GB of character data.

To see the column data type, use the following commands:
 

sqlplus avuser/secret
......
SQL> DESCRIBE WP_WORK_ITEM_HIST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ACTI_HIST_ID                              NOT NULL NUMBER(38)
 ACTI_HIST_DB                              NOT NULL NVARCHAR2(4)
 PROCI_ID                                  NOT NULL NUMBER(38)
 PROCI_DB                                  NOT NULL NVARCHAR2(4)
 ACTI_ID                                   NOT NULL NUMBER(38)
 ACTI_DB                                   NOT NULL NVARCHAR2(4)
 WI_ITERATION                              NOT NULL NUMBER(38)
 WORK_STATE_ID                             NOT NULL NUMBER(38)
 ROW_VERSION                               NOT NULL NUMBER(38)
 LU_ID                                              NVARCHAR2(240)
 LU_DATE                                   NOT NULL DATE
 COMPLETION_CODE                                    NUMBER(38)
 OPTIONAL_COMMENTS                                  NCLOB           <<<<<<<<<<<<<<
 FROM_STATE_ID                                      NUMBER(38)

To check the size of the data, run the following:

SELECT dbms_lob.getlength(<column>) FROM <table>;

For example:

SELECT dbms_lob.getlength(optional_comments) FROM wp_work_item_hist;

The Oracle TO_CHAR function always converts to VARCHAR2.  VARCHAR2 below Oracle 12c is restricted to 4000 bytes/characters. In 12c, you can extend the  VARCHAR2 data type to a size of 32k by setting MAX_STRING_SIZE = EXTENDED.

If changing the Oracle parameter MAX_STRING_SIZE to EXTENDED is not an option, or you are on Oracle 11.x,  you can shorten the input to TO_CHAR with this construct:

SELECT dbms_lob.substr(clob_column, 4000, 1 ) FROM <table>;

Notes

According to documentation on Oracle datatypes before Oracle 12c, variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2. BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.

For Oracle 12c, variable-length character string having maximum length size bytes or characters. You must specify size for VARCHAR2. Minimum size is 1 byte or 1 character. Maximum size is: 32767 bytes or characters if MAX_STRING_SIZE = EXTENDED 4000 bytes or characters if MAX_STRING_SIZE = STANDARD.