Using SQLPlus to Fetch Extensive CLOB Data

Feedback

Question:

Is there a method to retrieve large clob data from a table through sqlplus and print it to stdout? Although language-specific DB APIs offer a complete retrieval solution, relying solely on sqlplus has presented multiple issues.

  • The maximum size of the output buffer is 4000, which may be insufficient.
  • Character string
    buffer too small

Is it possible to download the entire data chunk from
oracle clob
fields, which can hold a maximum of 4GB, using sqlplus without any errors?

My wish is for the question to be easily understandable. It would be my preference to accomplish it without the need to inject PL/SQL procedures into the database.


Solution 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

All variable details can be found in this section.


  • long 2000000000

    defines the maximum limit of bytes that can be retrieved from a CLOB, which is 2gb.

  • linesize

    The maximum size of a line is 32k. If the line exceeds this size, it will be wrapped to the next row.
  • Retrieve the clob in chunks of 32k size.
  • Disable the formatting of the result page with MSDT code 1.
  • Please deactivate everything with the

    FEEDBACK,ECHO,TERMOUT

    identifier.
  • Specify the output destination as output_file.txt using

    Spool

    .


Solution 2:


My approach was limited by the maximum value of VARCHAR2 (
32767
), resulting in a restricted amount of retrievable data.

exec dbms_output.enable(32767);
set serveroutput on
DECLARE
    data_buffer VARCHAR2(32767);
BEGIN
    SELECT '' || CLOBDATA_VALUE || '' 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('no rows selected');
END;

The clob data is printed to the stdout without any modification.


Explanation

  • Here are two options that can be used to enlarge the output buffer size of sqlplus:

    exec dbms_output.enable(32767);


    set serveroutput on
  • The script retrieves the clob data and stores it in a variable named

    VARCHAR2

    . Then, the data is displayed using

    dbms_output.put_line()

    . If there is no data, the script is programmed to handle the exception and create
    error message
    .

Frequently Asked Questions