ERROR at line 191: ORA-01489: result of string concatenation is too long [duplicate]

Question

Select TO_CLOB(a)|| TO_CLOB(b)|| TO_CLOB(c) || TO_CLOB(d) 
  from table1

Above query is not spooling the data properly into text file.

whereas,

Select a||b||c||d 
  from table1.

is ending to

ERROR at line 191: ORA-01489: result of string concatenation is too long.

Please help !!!

Answer 1

VARCHAR2 are limited to 4000 bytes. If you get this error

ERROR at line 191: ORA-01489: result of string concatenation is too long.

Then it is pretty clear that the concatenation exceed 4000 bytes.

Now what to do ?

Your first solution to use CLOB instead is correct.

select TO_CLOB(a)|| TO_CLOB(b)|| TO_CLOB(c) || TO_CLOB(d) 

It seems like your real problem is saving to file

Above query is not spooling the data properly into text file.

While you did not post how to save the resulting clob to a file, I believe you are not doing it correctly. If you try to save to file the same way as you were doing it with VARCHAR2, you are doing it wrong.

You need to first use dbms_lob.read to read the clob from database, then use utl_file.put_raw to write to file.

DECLARE
    position NUMBER := 1;
    byte_length NUMBER := 32760;
    length NUMBER;
    vblob BLOB;
    rawlob RAW(32760);
    temp NUMBER;
    output utl_file.file_type;
BEGIN
    -- Last parameter is maximum number of bytes returned.
    -- wb stands for write byte mode
    output := utl_file.fopen('DIR', 'filename', 'wb', 32760);

    position := 1;
    select dbms_lob.getlength(yourLob)
    into len
    from somewhere
    where something;

    temp := length;

    select yourLob
    into vlob
    from somewhere
    where something;

    IF len < 32760 THEN
        utl_file.put_raw(output, vblob);
        -- Don't forget to flush
        utl_file.fflush(output);
    ELSE -- write part by part
        WHILE position < len AND byte_length > 0
        LOOP
           dbms_lob.read(vblob, byte_length, position, rawlob);

           utl_file.put_raw(output,rawlob);

           -- You must admit, you would have forgot to flush.
           utl_file.fflush(output); 

           position := position + byte_length;

           -- set the end position if less than 32000 bytes
           temp := temp - bytelen;
           IF temp < 32760 THEN
               byte_length := temp;
           END IF;
    END IF;
END;