How to create a complete text file from clob column in table-CodePudding

I have the problem that when I create the text file it only saves the first 32768 bytes but the file is larger and the rest of the information does not appear in the file.

This is the code in plsql that I am using, I think I am missing something so that the rest of the information is saved in the file.

The generated file

create table tab1 (
  col1 clob
);

CREATE OR REPLACE DIRECTORY DOCUMENTS AS '/process/files';

SET SERVEROUTPUT ON
DECLARE
  l_file    UTL_FILE.FILE_TYPE;
  l_clob    CLOB;
  l_buffer  VARCHAR2(32767);
  l_amount  BINARY_INTEGER := 32767;
  l_pos     INTEGER := 1;
BEGIN
  SELECT col1
  INTO   l_clob
  FROM   tab1
  WHERE  rownum = 1;

  l_file := UTL_FILE.fopen('DOCUMENTS', 'Sample2.txt', 'w', 32767);

  LOOP
    DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
    UTL_FILE.put(l_file, l_buffer);
    l_pos := l_pos   l_amount;
  END LOOP;
  UTL_FILE.fclose(l_file);
  
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(SQLERRM);
    UTL_FILE.fclose(l_file);
END;
/

CodePudding user response：

Since writing the clob to a file is a good "generic" sort of routine, I would write it as such

create or replace procedure clob_to_file( p_dir in varchar2,
 p_file in varchar2,
 p_clob in clob )
 as
 l_output utl_file.file_type;
 l_amt number default 32000;
 l_offset number default 1;
 l_length number default
nvl(dbms_lob.getlength(p_clob),0);
 BEGIN
 l_output := utl_file.fopen(p_dir, p_file, 'w', 32760);
 while ( l_offset < l_length )
 loop
 utl_file.put(l_output, dbms_lob.substr(p_clob,l_amt,l_offset) );
 utl_file.fflush(l_output);
 l_offset := l_offset   l_amt;
 end loop; utl_file.new_line(l_output);
 utl_file.fclose(l_output);
 end;
 /

create table test_tab ( col_id number, col_text clob );

declare
 l_col_text clob;
 begin
 for i in 1..5 loop
 insert into test_tab values
 ( i, empty_clob() )
 returning col_text into l_col_text;
 for i in 1 .. 10 loop
 dbms_lob.writeappend( l_col_text, 30001,
 rpad('*',30000,'*') || chr(10) );
 end loop;
 end loop;
 end;
 /

create or replace procedure dump_table_to_file
 (p_dir in varchar2,
 p_file_extn in varchar2 default '.txt',
 p_col_id in number default null)
 is
 BEGIN
 for x in ( select *
 from test_tab
 where col_id = nvl(p_col_id,col_id) )
 loop
 clob_to_file( p_dir,
 x.col_id || p_file_extn,
 x.col_text );
 end loop;
 END;
 /

exec dump_table_to_file( '/tmp' );

 ls -l /tmp/?.txt
-rw-r--r-- 1 oracle 300011 May 17 14:32 /tmp/1.txt
-rw-r--r-- 1 oracle 300011 May 17 14:32 /tmp/2.txt
-rw-r--r-- 1 oracle 300011 May 17 14:32 /tmp/3.txt
-rw-r--r-- 1 oracle 300011 May 17 14:32 /tmp/4.txt
-rw-r--r-- 1 oracle 300011 May 17 14:32 /tmp/5.txt

DBMS_LOB.GETLENGTH(COL_TEXT)
----------------------------
300010
300010
300010
300010
300010

Which is exactly what we expected. You'll get an exception on the fclose if your text does not have a newline every 32k.

CodePudding user response：

I was finally able to generate the full text file

I was missing the function UTL_FILE.fflush

SET SERVEROUTPUT ON
DECLARE
  l_file     UTL_FILE.FILE_TYPE;
  l_clob     CLOB;
  l_buffer   VARCHAR2(32767);
  l_amount   BINARY_INTEGER := 32767;
  l_max_size BINARY_INTEGER := 32767;
  l_pos      INTEGER := 1;
BEGIN
  SELECT col1
  INTO   l_clob
  FROM   tab1
  WHERE  rownum = 1;

  l_amount := l_max_size;
  l_file := UTL_FILE.fopen('DOCUMENTS', 'Sample2.txt', 'w', 32767);

  LOOP
    DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
    UTL_FILE.put(l_file, l_buffer);
    UTL_FILE.fflush(l_file);
    l_pos := l_pos   l_amount;

    EXIT WHEN l_amount < l_max_size;
  END LOOP;
  UTL_FILE.fclose(l_file);
  
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(SQLERRM);
    UTL_FILE.fclose(l_file);
END;
/

-rw-rw-rw-  1 oracle asmadmin 38105 May 18 00:45 Sample2.txt