I was trying to get a field from one table and inserted into another table.
At the source table, the value of the column may contain some special characters like "new line", "single quote", "Trade Mark" sign etc.
I am using the following code:

procedure aaa
is
...
ATR object_attribute.ATTRIBUTE_VALUE%type;
ATRL NUMBER := 0;
...
begin
...
SELECT LENGTH(ATTRIBUTE_VALUE) INTO ATRL
FROM OBJECT_ATTRIBUT
WHERE OBJECT_ID = OBJECT_REC.OBJECT_ID
AND OBJECT_TYPE = OBJECT_REC.OBJECT_TYPE
AND LANG_ID = 'EN'
AND ATTRIBUTE_ID = ATTRID;

LOG(1, 'ATTRVALULENGTH=' || ATRL);
IF ATRL > 1024
THEN
LOG(1, '-select-truncated=' || ATR || ' LENGTH=' || ATRL);
SELECT SUBSTR(ATTRIBUTE_VALUE,0,1024) INTO ATR
FROM OBJECT_ATTRIBUTE
WHERE OBJECT_ID = OBJECT_REC.OBJECT_ID
AND OBJECT_TYPE = OBJECT_REC.OBJECT_TYPE
AND LANG_ID = 'EN'
AND ATTRIBUTE_ID = ATTRID;
ATRL := LENGTH(ATR);
LOG(1, 'ATTR511VALUE=' || ATR || ' LENGTH=' || ATRL);
ELSE
SELECT rtrim(ATTRIBUTE_VALUE) INTO ATR
FROM OBJECT_ATTRIBUTE
WHERE OBJECT_ID = OBJECT_REC.OBJECT_ID
AND OBJECT_TYPE = OBJECT_REC.OBJECT_TYPE
AND LANG_ID = 'EN'
AND ATTRIBUTE_ID = ATTRID;
ATRL := LENGTH(ATR);
LOG(1, 'ATT---RVALUE=' || ATR || ' LENGTH=' || ATRL);
END IF;

EXCEPTION
WHEN OTHERS
THEN
LOG(4, 'OBJECT:' || OBJECT_REC.OBJECT_ID || 'ATTRIBUTE:' || ATTRID || ' ERROR:' || sqlerrm);
ATTRVALUE := '';
end;

It seems that
SELECT rtrim(ATTRIBUTE_VALUE) INTO ATR
FROM OBJECT_ATTRIBUTE
giving error "Inserted value too large for column" when there are some special characters in object_attribute.attribute_value.
If I remove those special characters, then the error will be gone.

Would anybody helps me or tell me how do I handle this case? I would like to preserve the characters if possible. I tried to define ATR to be "VARCHAR2(2000) and it still get the same error even though the length is only 600.

thanks in advance for any tips.
Dave Wang
Can