oracle-databasecsvplsqlutl-file

Trying to write output to a CSV on a directory, failing with PLS-00302


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?


Solution

  • 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;