I'm attempting to output some monitoring information into a .csv file. I have to stick with the "Basic" framework of the code. The issue is getting it to output to .csv/getting the code to compile.
I've tried various ways of doing this, now I'm stuck I mostly find myself moving quotes and double quotes around.
create or replace procedure WRITE_EST_SIZE_01 is
file_handle UTL_FILE.file_type;
begin
file_handle := utl_file.fopen('ESTIMATES_CSV',
'csv_filename' ||
to_char(sysdate,'MONYYYY')||'.csv',
'w', 32767);
for rws in (SELECT 'OWNER' || ',' ||
'SEGMENT_NAME' || ',' ||
'U' || ',' ||
'SUM_BYTES'
FROM
union ALL
select /*+ parallel*/
s.owner || ',' ||
s.segment_name || ',' ||
'U' || ',' ||
sum(s.bytes)/1024/1024
from DBA_SEGMENTS s
where s.owner = (select distinct targetschema
from pdu.pdu_table) and
s.segment_name in (select table_name
from another_table) and
s.segment_type LIKE '%TABLE%'
group by s.owner, s.segment_name
union all
select /*+ parallel*/
i.table_owner || ',' ||
i.table_name || ',' ||
'I' || ',' ||
sum(s.bytes)/1024/1024
from DBA_SEGMENTS s,
DBA_INDEXES i
where i.table_owner = (select distinct targetschema
from pdu.pdu_table) and
i.table_name in (select table_name
from another_table) and
i.owner = s.owner and
i.index_name = s.segment_name and
s.segment_type like '%INDEX%'
group by i.table_owner, i.table_name
union all
select /*+ parallel*/
l.owner || ',' ||
l.table_name || ',' ||
'L' || ',' ||
sum(s.bytes)/1024/1024
from DBA_SEGMENTS s,
ALL_LOBS l
where l.owner = (select distinct targetschema
from another_table) and
l.table_name in (select table_name
from another_table) and
l.owner = s.owner and
l.segment_name = s.segment_name
group by l.owner, l.table_name
--order by 1, 2)
loop
utl_file.put_line(file_handle,
rws.OWNER || ',' ||
rws.SEGMENT_NAME || ',' ||
rws.U || ',' ||
rws.SUM_BYTES -- your columns here
);
end loop;
utl_file.fclose(file_handle);
end WRITE_EST_SIZE_01;
This actually won't compile, but complains that rws.OWNER should be declared. It compiles if I put all the rws. in quotes, but then the csv output is overwritten with whatever is in quotes. Can anyone see a way of doing this whereby it actually "Will" dump the output of the sql to a .csv?
In your SQL you're creating a concatenated string when it appears you just wanted to fetch the individual fields. I suggest:
create or replace procedure WRITE_EST_SIZE_01 is
file_handle UTL_FILE.file_type;
begin
file_handle := utl_file.fopen('ESTIMATES_CSV',
'csv_filename' ||
to_char(sysdate,'MONYYYY')||'.csv',
'w', 32767);
for rws in (select s.owner,
s.segment_name,
'U' AS FLAG,
sum(s.bytes)/1024/1024 AS SUM_BYTES
from DBA_SEGMENTS s
where s.owner = (select distinct targetschema
from pdu.pdu_table) and
s.segment_name in (select table_name
from another_table) and
s.segment_type LIKE '%TABLE%'
group by s.owner, s.segment_name
union all
select i.table_owner AS OWNER,
i.table_name AS SEGMENT_NAME,
'I' AS FLAG,
sum(s.bytes)/1024/1024 AS SUM_BYTES
from DBA_SEGMENTS s,
DBA_INDEXES i
where i.table_owner = (select distinct targetschema
from pdu.pdu_table) and
i.table_name in (select table_name
from another_table) and
i.owner = s.owner and
i.index_name = s.segment_name and
s.segment_type like '%INDEX%'
group by i.table_owner, i.table_name
union all
select l.owner,
l.table_name AS SEGMENT_NAME,
'L' AS FLAG,
sum(s.bytes)/1024/1024 AS SUM_BYTES
from DBA_SEGMENTS s,
ALL_LOBS l
where l.owner = (select distinct targetschema
from another_table) and
l.table_name in (select table_name
from another_table) and
l.owner = s.owner and
l.segment_name = s.segment_name
group by l.owner, l.table_name
--order by 1, 2)
loop
utl_file.put_line(file_handle,
rws.OWNER || ',' ||
rws.SEGMENT_NAME || ',' ||
rws.FLAG || ',' ||
rws.SUM_BYTES -- your columns here
);
end loop;
utl_file.fclose(file_handle);
end WRITE_EST_SIZE_01;