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)
)

Show Image In Form Form Row Data

1. Creating Table 
CREATE TABLE WEB_IMAGE_TEMP1
(
  SRLNO      NUMBER,
  CUST_ID    NUMBER,
  CUST_IMG   LONG,
  MIME_TYPE  VARCHAR2(2000 BYTE)
)

2. Create Query To Show Image In Forms (Specially In Oracle Apex. )

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)
)