Skip to Main Content
  • Questions
  • How to spool CLOB data completely into a single line to a .csv file using Sqlplus?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Bhargav.

Asked: June 02, 2022 - 1:48 pm UTC

Last updated: June 30, 2022 - 4:52 pm UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Viewed 10K+ times! This question is

You Asked

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

and Chris said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here