I need to spool the result of a select statement to a file. My basic wish is to spool the result directly to a file from within a trigger each time the table or view is updated (and secondary via a function).
How do I do that?
create or replace trigger logon_status_upd1
instead of update on logon_status for each row
select initials from logon_status;
This is not a particularly good idea, but - in certain circumstances, it may do the job: spool result of DBMS_OUTPUT.PUT_LINE as in this example:
SQL> create or replace procedure prc_spool is
3 for cur_r in (select dname from dept)
6 end loop;
SQL> show errors
SQL> set serveroutput on
SQL> spool test.txt
SQL> exec prc_spool;
PL/SQL procedure successfully completed.
SQL> spool off;
SQL> $type test.txt