ORA-02429: cannot drop index used for enforcement

Cause : It is an unique index that enforces unique constraint. It can not drop

DROP INDEX SY_CURRENT_USERCOMP_UNIQUE;

Action : You need to drop unique constraint BY

ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME

ALTER TABLE SY_CURRENT_USER DROP CONSTRAINT SY_CURRENT_USERCOMP_UNIQUE;

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