CODEWITHSUNDEEP
oracle-databasecsvplsqlutl-file
guru raj
guru raj

Reputation: 39

Is there any way to use the utl_file to generate a csv from data more than 32000 in oracle plsql

.. FILE1 := UTL_FILE.FOPEN('DIR','cg.csv','w',32000); .. we are generating a csv file which has concatenated value of many columns of a view. Is there any way to use the utl_file to generate a csv from data more than 32000 in oracle plsql

Upvotes: 0

Views: 1968

Answers (3)

user272735
user272735

Reputation: 10648

Yes there is a way - use clob data type instead of varchar2. clob maximum size in PL/SQL is 128TB.

In your PL/SQL code first collect data to a temporary clob. Second iterate the clob in chunks and feed those to utl_file.

Below is a random internet code snippet that writes an arbitrary clob to a user defined file:

procedure save (
  p_text in clob,
  p_path in varchar2,
  p_filename in varchar2
) as
  v_lob_temp blob;
begin
  --
  -- exit if any parameter is null
  --
  if    p_text is null
     or p_path is null
     or p_filename is null then
    return;
  end if;

  --
  -- convert a clob to a blob
  --
  declare
    v_dest_offset pls_integer := 1;
    v_src_offset pls_integer := 1;
    v_lang_context pls_integer := dbms_lob.default_lang_ctx;
    v_warning pls_integer := dbms_lob.no_warning;
  begin
    dbms_lob.createtemporary(lob_loc => v_lob_temp,
                             cache => true,
                             dur => dbms_lob.call);

    dbms_lob.converttoblob(dest_lob => v_lob_temp,
                           src_clob => p_text,
                           amount => dbms_lob.lobmaxsize,
                           dest_offset => v_dest_offset,
                           src_offset => v_src_offset,
                           blob_csid => dbms_lob.default_csid,
                           lang_context => v_lang_context,
                           warning => v_warning);
    -- TODO raise (what?) when warning
  end;

  --
  -- write a blob to a file
  --
  declare
    v_lob_len pls_integer;
    v_fh utl_file.file_type;
    v_pos pls_integer := 1;
    v_buffer raw(32767);
    v_amount pls_integer := 32767;
  begin
    v_fh := utl_file.fopen(p_path, p_filename, 'wb', 32767);

    v_lob_len := dbms_lob.getlength(v_lob_temp);

    while v_pos < v_lob_len loop
      dbms_lob.read(v_lob_temp, v_amount, v_pos, v_buffer);

      utl_file.put_raw(file => v_fh,
                       buffer =>v_buffer,
                       autoflush => false);

      v_pos := v_pos + v_amount;
    end loop;

    utl_file.fclose(v_fh);
    dbms_lob.freetemporary(v_lob_temp);
  end;

end;

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

declare
FILEHANDLE           UTL_FILE.FILE_TYPE;
WRITEMESSAGE   varchar2(200);
longLine varchar2(32767);
newline char(2) :=  CHR(13) || CHR(10);
begin
longLine :=  LPAD('aaaa', 32766,'x');
FILEHANDLE        := UTL_FILE.FOPEN('XMLDIR','lonLineFile.txt','wb',32767);
for i in 1 .. 5 loop
  UTL_FILE.PUT_RAW (filehandle,UTL_RAW.CAST_TO_RAW (longLine),true); 
  UTL_FILE.PUT_RAW (filehandle,UTL_RAW.CAST_TO_RAW (longLine),true); 
  UTL_FILE.PUT_RAW (filehandle,UTL_RAW.CAST_TO_RAW (longLine),true); 
  UTL_FILE.PUT_RAW (filehandle,UTL_RAW.CAST_TO_RAW (newline),true); 
end loop;
  UTL_FILE.FCLOSE(filehandle);
end;
/

Open file in 'wb' write byte mode. Next write raw to file and end of line char.

Upvotes: 1

The Fabio
The Fabio

Reputation: 6250

According to Oracle's documentation for the function UTL_FILE.FOPEN:

FOPEN max_linesize parameter must be a number in the range 1 and 32767

so you can increase it to 32767, but not further then that.

You might want to consider creating multiple files.

Upvotes: 0

Related Questions