Retrieve large clob data using sqlplus

Question

How to completely retrieve large clob data from a table using sqlplus to the stdout? There is a way to get it completely using language specific DB APIs. But when I try to get it using purely sqlplus, I've faced several problems such as,

• Output buffer is too small (4000 is max)
• Character string buffer too small

Since oracle clob fields can contain 4GB (max) of data, is there any correct way to get the complete data chunk using sqlplus? Can I download it as a file?

I hope that the question is clear. I prefer if I can do it without injecting PL/SQL procedures to the database.

Answer 1

1) First table and clob.

create table large_clob(a clob);
insert into large_clob values( dbms_xmlgen.getXml('select * from dba_objects'));

2) Run code in sqlplus

set linesize 32767 long 2000000000 longchunksize 32767 PAGESIZE 0 FEEDBACK OFF ECHO OFF TERMOUT OFF
Spool output_file.txt
  select a from large_clob;
spool off

Description of all variables is here

• long 2000000000 - specifies how many bytes of CLOB to retrieve. (2gb is max)
• linesize size of line (32k is max). size of line. If line exceeds the size , the line will be wrapped to next row
• longchunksize 32k - clob will be retrieved in chunks, where the chunk size is 32k
• PAGESIZE 0 - disbale result page fomrationg
• FEEDBACK,ECHO,TERMOUT - disable all of this.
• Spool redirect output to output_file.txt

Answer 2

This is how I did it. But in here data which can be retrieved is limited to the max value of VARCHAR2 (32767).

exec dbms_output.enable(32767);
set serveroutput on
DECLARE
    data_buffer VARCHAR2(32767);
BEGIN
    SELECT '<BGN>' || CLOBDATA_VALUE || '<END>' into data_buffer 
    FROM DUMMY_TABLE
    WHERE ID='DUMMY_ID';
    dbms_output.put_line(data_buffer);
EXCEPTION
    when no_data_found then
    dbms_output.put_line('<BGN>no rows selected<END>');
END;

It prints the clob data as it is to the stdout.

Explanation

• Following two items increase the output buffer size of the sqlplus exec dbms_output.enable(32767); set serveroutput on
• What the script does is select the clob data in to a VARCHAR2 variable and print it via dbms_output.put_line(). Whenever there is no data (no_data_found exception occurs), the exception will be handled and error message will be generated.