Dear Tom,
One of my tables uses the clob datatype and has a total length of roughly 70000 characters.
The data is truncating at 32767 characters when I'm spooling it to a.psv file, because of the set linesize 32767.
I've used the following code.
SET HEADING OFF
SET VERIFY OFF
SET WRAP OFF
SET TERMOUT OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SET TRIMOUT ON
SET FEEDBACK OFF
SET ECHO OFF
SET LONG 20000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 32767
SET SQLBLANKLINES ON
Could you kindly help me with spooling clob data in a single line using sqlplus or any other approach?
version:
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Jun 2 19:10:10 2022
Version 21.3.0.0.0
Thanks.
Bhargav
I don't know of a what around this - once you have text that is longer than the LINESIZE SQL*Plus wraps or truncates it (depending on your setting).
You can use UTL_file to write large CLOB on a single line - he's an example:
declare
j clob;
procedure write_clob_to_file (
clob_data in clob
) as
l_file utl_file.file_type;
l_buffer varchar2(32767);
l_amount binary_integer := 32767;
l_pos integer := 1;
begin
l_file := utl_file.fopen ( 'TMP', 'test.csv', 'wb', 32767 );
loop
begin
dbms_lob.read (clob_data, l_amount, l_pos, l_buffer);
exception
when no_data_found then
exit;
end;
utl_file.put_raw ( l_file, utl_raw.cast_to_raw ( l_buffer ) );
l_pos := l_pos + l_amount;
end loop;
utl_file.fclose(l_file);
end write_clob_to_file;
begin
select json_arrayagg (
object_name returning clob
) j
into j
from dba_objects
where owner = 'SYS';
write_clob_to_file ( j );
end;
/
Note this write to the database server wherever the directory TMP points to.