You Asked

Hello Tom,

I need help on below question please help me

Single sensor data from different tables should be merged in one excel.
File should not exceed 4mb size and records should not exceed 100000 records.

IF single sensor data from different tables exceeds more than 4mb file or 100000 records limit then the remaining data should go to second excel.

the csv to be export in to structure folder like below Year  Month  Bi-weekly/Weekly

and Connor said...

You could use some logic within UTL_FILE to keep track of size and row counts and start a new file accordingly

declare

  l_file  utl_file.file_type;
  l_idx   int := 1;
  l_text  varchar2(32767);
  l_size  int := 0;
  l_rows  int := 0;
begin
  l_file := utl_file.fopen(location     => 'temp',
                           filename     => 'file'||l_idx||'.csv',
                           open_mode    => 'w',
                           max_linesize => 32767);
  for c in 
    ( select ... from table1 union all
      select ... from table2 union all
      ...
    )
  loop
   
    if l_rows = 100000 or
       l_size > 4*1024*1024 
    then
      utl_file.fclose(l_file);
      l_idx := l_idx + 1;
      l_rows := 0;
      l_size := 0;
      l_file := utl_file.fopen(location     => 'temp',
                               filename     => 'file'||l_idx||'.csv',
                               open_mode    => 'w',
                               max_linesize => 32767);
    end if;
    
    l_text := c.col1    || ',' ||
              c.col2    || ',' ||
              c.col3    || ',' ||
              c.col4    || ',' ||
              c.col5    || ',' ||
              c.col6    || ',' || 
              ...
              ...;
    l_rows := l_rows + 1;
    l_size := l_size + length(l_text);
    
    utl_file.put_line(l_file,
                      
                      c.deptno);
  end loop;
  utl_file.fclose(l_file);
  
exception
  when others then
    utl_file.fclose(l_file);
    raise;
end;
/

If you want to write out fully XLSX compatible sheets, you'd need a 3rd party solution.

There are also PLSQL versions out there, but thats a fairly big undertaking

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

it was very helpful

javid, August 13, 2020 - 2:15 pm UTC

Thank you so much appreciate your help your a rock star.

August 15, 2020 - 6:43 am UTC

Oracle writes to directory objects, so you would need to have a directory set up for each *pre-existing* folder that you want to write to.

Then it is just a case of changing the directory name as required.

Need lil help ( adding to my previous script only)

javid, August 13, 2020 - 2:33 pm UTC

Hello Tom ,

Thank you so much for help. And i need lil more help .

This is related to last question only which i asked . And you sent script also. Which was great help. Now i need lil more help my question is we need data to be export in .csv and structure should be in folder like below Year  Month  Bi-weekly / Weekly ?

Can we add / modify the script you sent me and add above requirement that data should be export into .csv in folder structure YEAR / MONTH / BIWEEKLY / WEEKLY?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

it was very helpful

Need lil help ( adding to my previous script only)

More to Explore

PL/SQL demos

PL/SQL docs

exporting csv within the limits of excel

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

it was very helpful

Need lil help ( adding to my previous script only)

More to Explore

PL/SQL demos

PL/SQL docs