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 !!!
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;
How about increasing the value for LONG? You might have to increase the long variable to a higher value. Click here for detailed description.
Example:
SET LONG 100000;
SPOOL test_clob.txt
SELECT to_clob(lpad('A',4000,'A'))
||'B'
||to_clob(lpad('C',4000,'C'))
||'D'
||to_clob(lpad('E',4000,'E'))
||'F'
FROM dual;
SPOOL OFF;
Your second query returns error because, The concat(||) operator in the query is trying to return varchar2, which has limit of 4000 characters and getting exceeded.
SQL*Plus hardcode linesize 81 when displaying CLOB, there seems no way around it. Therefore if you want to generate a csv file to be loaded to other databases, you will have a problem of parsing these extra newlines.
The ultimate solution is to use PL/SQL. For example, to genarate a comma delimited csv file from table "xyz", use the following code:
set lin 32766
set serveroutput on size unlimited
DECLARE
TYPE arraytable IS TABLE OF xyz%ROWTYPE;
myarray arraytable;
CURSOR c IS
select * from xyz ;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO myarray LIMIT 10000;
FOR i IN 1 .. myarray.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(
myarray(i).col1||','||
myarray(i).col2||','||
myarray(i).col3||','||
myarray(i).col4;
END LOOP;
EXIT WHEN c%NOTFOUND;
END LOOP;
END;
/
A bonus of this approach is that this even works with LONG datatype!
Try xmlagg function. That worked well for me when I encountered a similar issue.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm
