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
Originally Published: 2016-06-30
Article Number
Applies To
RSA Version/Condition: 6.8.1 and higher
Issue
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 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
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.
Related Articles
After applying a patch, an 'ORA-06502: PL/SQL: numeric or value error: character string buffer too small' error occurs whe… Number of Views How to convert the date field number from a request form to a human readable date in RSA Via Lifecycle and Governance Number of Views How to query a public database schema table for Segregation of Duties (SOD) violations in RSA Identity Governance & Lifecycle Number of Views AFX Requests fails to process as the Usage Store Manager is full in RSA Governance & Lifecycle Number of Views Review generation fails with 'ORA-01427: single-row subquery returns more than one row' in RSA Identity Governance & Lifec… Number of Views
Trending Articles
RSA Authentication Manager 8.9 Release Notes (January 2026) RSA Release Notes for RSA Authentication Manager 8.8 Generate a Certificate Signing Request (CSR) for the Web Tier RSA SecurID Software Token 4.1.2 and 4.2.1 for Mac OS X displays: No token storage device was detected. Verify that the de… RSA Authentication Manager 8.8 Security Configuration Guide
Don't see what you're looking for?
