Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: Spool from a function or trigger

    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?

    Something like:

    create or replace trigger logon_status_upd1
    instead of update on logon_status for each row
    begin
    spool c:\temp\test.txt
    select initials from logon_status;
    spool off
    end;

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Use utl_file to do what you want. Spool will NOT work in procedures.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Code:
    SQL> create or replace procedure prc_spool is
      2  begin
      3    for cur_r in (select dname from dept)
      4    loop
      5      dbms_output.put_line(cur_r.dname);
      6    end loop;
      7  end;
      8  /
    
    Procedure created.
    
    SQL> show errors
    No errors.
    SQL> set serveroutput on
    SQL> spool test.txt
    SQL> exec prc_spool;
    ACCOUNTING
    RESEARCH
    SALES
    OPERATIONS
    
    PL/SQL procedure successfully completed.
    
    SQL> spool off;
    SQL> $type test.txt

  4. #4
    Join Date
    Jun 2003
    Posts
    81

    Still need to spool from a trigger

    Thanks, but I still need to spool to the file automaticly.

    I was hoping to create a logon-trigger which could write the contents of a view at each logon. How do I do that?

  5. #5
    Join Date
    Dec 2007
    Posts
    253
    As Bill said, use UTL_FILE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •