Results 1 to 5 of 5

Thread: Help with Spool

  1. #1
    Join Date
    Mar 2004

    Unanswered: Help with Spool

    I want to execute the spool command only on a particular date that is set on a table. How can i create a job for this to run every month of that date? And how to create the spool file without the field names and feedback (eg. 2 rows selected.) ?

  2. #2
    Join Date
    Apr 2003
    Greenville, SC (USA)
    Can you give an example of what you're trying to do ??? I'm not sure I completely understand ...


  3. #3
    Join Date
    Mar 2004
    not sure what you are trying to do with the date but here are my general settings to turn off the extra junk so you scripts will run clean.

    set echo off
    set verify off
    set heading off
    set feedback off
    set newpage none
    set line 200
    ttitle off

  4. #4
    Join Date
    Dec 2003
    Oklahoma, USA
    If I understand correctly, try this:

    SQL> SET echo off verify off feedback off heading off newpage none;
    SQL> SPOOL run_first.sql
    SQL> SELECT 'spool ' || date_column || '.TXT' FROM myTable;
    SQL> SELECT 'select col1, col2 FROM myTable;' FROM dual;
    SQL> SELECT 'spool off;' FROM dual;
    SQL> @run_first
    First line, takes out the extra output that SQL*Plus usually prints out so it won't appear in the spool file.

    Second line generates our temp file.

    Third line outputs a line with your date as the file name -- does not create the file yet.

    Fourth line outputs the query you will run and store in the date designated file -- query is not run yet.

    Fifth line puts a spool off command in the script file.

    Sixth line shuts off spooling to our temp file.

    Seventh line then executes the temp file, which will create your date designated file and run your query.

    Only problem is that spool will automatically repeat what is input to the spool file so inside your date designated file, you will see a couple of lines that say:

    SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored.

    Hope this helps


  5. #5
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Below will create a "variable" spool file name in the form of "SID_YYMONDD.dat"
    ================================================== =====
    set verify off echo off term off heading off pause off

    spool tmp7_spool.sql
    select 'spool '||name||'_'||to_char(sysdate,'yymondd')||'.dat'
    from sys.v_$database;
    spool off

    HTH & YMMV
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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