Error- Ora-22835: Buffer Too Small for Clob to Char or Blob to Raw Conversion

Error- ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion

I got this worked by using the option described in

http://www.dba-oracle.com/t_convert_blob_varchar_datatype.htm

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

Most likely it comes from one of these expressions:

CASE
    WHEN TO_CHAR(INSTALLER_SIGNATURE) = null
    THEN null
    ELSE 'NULL'
END

First, they don't work. ... = NULL never yields TRUE. Is IS NULL instead.
There is no need to use TO_CHAR(), IS NULL works for any data type.

And you can write it shorter as

INSTALLER_SIGNATURE = NVL2(INSTALLER_SIGNATURE, 'NULL', NULL)

NB,

REGEXP_LIKE(substr(CUST_DECLARATION_AUTH_ID,1,1),'[A-Za-z]') 
and REGEXP_LIKE(substr(CUST_DECLARATION_AUTH_ID,-1,1), '[A-Za-z]') 

can be written as

REGEXP_LIKE(CUST_DECLARATION_AUTH_ID,'^[A-Za-z].*[A-Za-z]$')

ORA-22835: Buffer too small for CLOB to CHAR... error when trying to render Dynamic PL/SQL Content Region - APEX 21.2

Loop through the clob and print in chunks of 4000 characters - that shouldn't error out. Here is sample code. Note that htp.prn is what you need, not htp.p. The latter prints a newline character after the string and that could produce unexpected results and hours of bug hunting...

DECLARE
  l_clob  CLOB;
  l_query VARCHAR2(32000);
  l_amt   INTEGER := 4000;
  l_pos   INTEGER := 1;
  l_buf   VARCHAR2(4000);  
BEGIN
  l_query := <your_query>;
  
  EXECUTE IMMEDIATE l_query INTO l_clob;
  LOOP
      BEGIN
      dbms_lob.read(
                  l_clob,
                  l_amt,
                  l_pos,
                  l_buf
      );
      l_pos := l_pos + l_amt;
      -- need htp.prn since htp.p generates a newline char at the end.
      htp.prn(l_buf);
      EXCEPTION
      WHEN no_data_found THEN
          EXIT;
      END;
  END LOOP;  
END;

---

Related Topics