Spooling Serveroutput into a CSV file

Question

I got the task to update a rather big file that consists of many update and delete statements.

First things first would be to check, which statements are actually needed / used.

I would like to spool this into a separate file, but it is difficult to get it into a nice format.

For example:

set serveroutput on
spool xxx.csv

update xx set yy where a = b;

Creates a file like:

sql: update xx.....

1100 rows updated.

The closest i got to my desired output is to use something like:

spool xxx.csv

select 'update xx set yy where a = b;' query, count(x) count from xx where (update where clause)

This would work mostly well (except for multiline queries), but it would require me to rewrite all update / delete statements and there are a lot.

Does anyone have an idea how I could solve this the best way? The best outcome would be a file like:

Query            Count
update xx ...    1100

Thanks in advance!

Answer 1

You can use SQL%ROWCOUNT and the below select to achieve your requirement

    DECLARE
      l_sql_text  VARCHAR2(32767);
      l_sql_count NUMBER;
    BEGIN
      insert into tttt values(4);

      l_sql_count:= SQL%rowcount;
      SELECT
             (
                    SELECT t2.sql_fulltext
                    FROM   v$sql t2
                    WHERE  t1.prev_sql_id = t2.sql_id
                    AND    t1.prev_child_number = t2.child_number ) prev_sql_fulltext
      INTO   l_sql_text
      FROM   v$session t1
      WHERE  t1.audsid = Sys_context('userenv', 'sessionid');

      dbms_output.put_line('Query,Count');

      dbms_output.Put_line(l_sql_text
      ||','
      ||l_sql_count);
    END;

Output

Query,Count
INSERT INTO TTTT VALUES(4),1