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

Question

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?

score 2 · Answer 1 · answered Jan 23 '19 at 12:32

Your cursor query is doing the concatenation in each branch of the union, so if you ran that standalone you'd see a result set with a single column. When you try to process the loop you're trying to look for the individual owner/segment/etc. - but they are not part of the projection from that cursor query.

If you give the single generated column value an alias, in at least the first branch:

SELECT 'OWNER'||','||'SEGMENT_NAME'||','||'U'||','||'SUM_BYTES' AS CSV_TEXT

or more simply:

SELECT 'OWNER,SEGMENT_NAME,U,SUM_BYTES' AS CSV_TEXT

then in your loop you can refer to that alias:

utl_file.put_line(file_handle, rws.CSV_TEXT);

Although it would probably be simpler just to write the header row out to the file directly before your cursor loop, instead of making it part of that query:

utl_file.put_line(file_handle, 'OWNER,SEGMENT_NAME,U,SUM_BYTES');

You could then keep the concatenation in the remaining union branches with the same single column-value alias; or have the union branches get the raw columns (owner etc.) without concatenating, and then keep the concatenation inside the loop. [As @BobJarvis' answer is doing!] But don't do both...

score 2 · Accepted Answer · answered Jan 23 '19 at 12:33

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;

You could add `utl_file.put_line(file_handle, 'OWNER,SEGMENT_NAME,U,SUM_BYTES');` before the loop to generate the header row? — Alex Poole, Jan 23 '19 at 12:37
@AlexPoole Thanks Alex. It compiles, until I add in the utl_file.put_line(file_handle, 'OWNER,SEGMENT_NAME,U,SUM_BYTES'); before the loop. Is that supposed to go in, just stand alone, or am I meant to remove a bit before adding that in? — Scouse_Bob, Jan 23 '19 at 22:33
@Dave - I didn't mean immediately before they keyword `loop`, I meant tbefore the whole loop structure - so it goes between the `utl_file.fopen(...);` and `for rws in ...`. — Alex Poole, Jan 23 '19 at 23:11
@AlexPoole - Thanks Alex I have this working now. Appreciate your help!! — Scouse_Bob, Jan 24 '19 at 00:09

score 2 · Answer 3 · answered Jan 23 '19 at 12:34

When you write:

   ) loop
utl_file.put_line(file_handle, rws.OWNER||','||rws.SEGMENT_NAME||','||rws.U||','||rws.SUM_BYTES);

you use column names which should be described in the query inside for rws in (select ...) loop. Currently, that SELECT statement has only one column whis automatically generated name. You need to change it to:

SELECT 'OWNER' owner, 'SEGMENT_NAME' segment_name, 'U' u, 'SUM_BYTES' sum_bytes
  FROM dual
 union all
select /*+ parallel*/
       s.owner, s.segment_name, 'U', sum(s.bytes)/1024/1024 
  from ...

Also, you need to change all other subqueries in this way.

Or, you can keep this query as is, except a small change:

for rws in (SELECT 'OWNER,SEGMENT_NAME,U,SUM_BYTES' row_data
                FROM
              union ALL

And change the last line:

...
) loop
utl_file.put_line(file_handle, rws._row_data);

Thank you very much for your help on this. Great work and very helpful. — Scouse_Bob, Jan 24 '19 at 04:18

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

3 Answers3