Retrieve large clob data using sqlplus

4
votes

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.

2
oraclesqlplusclob

2 Answers

9
votes

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
1
votes

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.