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.

Arkadiusz Łukasiewicz · Answer 1 · 2018-03-29T07:37:25.597

14

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

edited Mar 29 '18 at 07:37

answered Mar 28 '18 at 09:40

Arkadiusz Łukasiewicz

6,241
1
11
17

Is it limited to the 32767 character limit? – Amith Chinthaka Mar 28 '18 at 10:27
1

It is size limit for line in clob.If line is longer it will be splitted to two lines. [SET System Variable Summary](https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm) – Arkadiusz Łukasiewicz Mar 28 '18 at 10:58
When it split the line into two lines, it also add an additional new line also. The generated file contain the sql which I executed also. Other than those problems, it does what is expected. I recommend the usage of `set heading off` before using this. – Amith Chinthaka Mar 28 '18 at 12:08
1

While this code may answer the question, it would be better to include some context, explaining how it works and when to use it. Code-only answers are not useful in the long run. – piman314 Mar 28 '18 at 12:33
@ncfirth As far as I understood, what it simply does is redirecting the output of sqlplus terminal to a file. – Amith Chinthaka Mar 29 '18 at 03:42
@ArkadiuszŁukasiewicz As I noticed, clob data is separated to two lines even if it doesn't exceed the 32767 chunk size. – Amith Chinthaka Mar 29 '18 at 03:45

Amith Chinthaka · Answer 2 · 2018-03-29T03:40:32.423

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.

Retrieve large clob data using sqlplus

2 Answers2

Linked