ORA-01489: result of string concatenation is too long

Select ('data:image/png;base64,')||IMAGE Value
From(
WITH xml AS
     (SELECT DBMS_XMLGEN.getxmltype
                        ('select cust_img from MEMIMG.WEB_IMAGE_TEMP1')
                                                                       AS xml
        FROM DUAL)
SELECT EXTRACTVALUE (xs.object_value, '/ROW/CUST_IMG') AS image
  FROM xml x
    , TABLE (XMLSEQUENCE (EXTRACT (x.xml, '/ROWSET/ROW'))) xs)
                                                                                  *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

Cause : This problem occurred when it can try to return data which are grater then 4000 characters. Varchar2 can return maximum 4000 characters.

This problem can also occurred when we try to CONCAT a VARCHAR2 with CLOB.


Solution: You can use TO_CLOB For Converting character data to clob values.


Select '<img src="'||Value||'"/>' Image
From (
Select TO_CLOB('data:image/png;base64,')||IMAGE Value
From(
WITH xml AS
     (SELECT DBMS_XMLGEN.getxmltype
                        ('select cust_img from MEMIMG.WEB_IMAGE_TEMP1')
                                                                       AS xml
        FROM DUAL)
SELECT EXTRACTVALUE (xs.object_value, '/ROW/CUST_IMG') AS image
  FROM xml x
    , TABLE (XMLSEQUENCE (EXTRACT (x.xml, '/ROWSET/ROW'))) xs)
)