|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:
To check the size of the data, run the following:
SELECT dbms_lob.getlength(<column>) FROM <table>;
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.