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?

BELOW IS THE LAST CONVERSATION WE HAD LAST TIME . AND YOU SENT ME BELOW ANSWER AND SCRIPT . AND I WANT TO ADD dd / modify the script you sent me and add above requirement that data should be export into .csv in folder structure YEAR / MONTH / BIWEEKLY / WEEKLY?


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 we 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;
/