Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    57

    Question Unanswered: Spooling a SQL script with incremental names

    I'm looking for a way to spool a SQL script to a given file Ex. output.txt The output files might not be removed from the directory at a regular basis. Rather than having the "output.txt" be overwritten I'd like to have the file be called something else when a file is already present. Ex. output.001, output.002 output.003 etc...

    Or perhaps a even better naming convention would be to have the file named with a timestamp. Ex. 20050122.out, 20050123.out, 20050124.out etc...

    I think the second option is a better solution but I'll take either.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can generate the spool filenames like this:
    Code:
    column filename new_value spoolfile
    
    select to_char(sysdate,'YYYYMMDD')||'.out' filename
    from dual;
    
    spool &spoolfile

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    will something like the following work for you

    Code:
    set verify off
    set echo off
    set term off
    set heading off
    
    spool tmp7_spool.sql
            select 'spool '||name||'_'||to_char(sysdate,'yymondd')||'.dat' 
            from sys.v_$database;
    spool off
    @tmp7_spool.sql
    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.

  4. #4
    Join Date
    Nov 2004
    Posts
    57

    Question

    I wasn't very specific with my original post in this thread (It was the weekend and I wasn't on at my workstation). I'm looking to incorporate this output functionality into my SQL script that uses a cursor.

    I tried both of your suggestions but they didn't work for me. I think it's because I need to declare something or didn't add it into the select statement correctly.

    Attached is my SQL script to give you an idea as to what I'm talking about. Tony this might look familiar to you.
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    SPOOL is a SQL Plus command, it is not valid inside PL/SQL. You must therefore issue the SPOOL either before or after the PL/SQL block.

  6. #6
    Join Date
    Nov 2004
    Posts
    57
    The error message that I was getting was due to the fact that the table I was attempting to update was in another instance. The problem was corrected when I switched to the correct instance.

    The script works now and I'd like to tank Tony and Littlefoot for their help again. Thanks guys!

  7. #7
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Quote Originally Posted by andrewst
    You can generate the spool filenames like this:
    Code:
    column filename new_value spoolfile
    
    select to_char(sysdate,'YYYYMMDD')||'.out' filename
    from dual;
    
    spool &spoolfile
    Nice tip, Tony!
    I never thought that would be possible.

    Any way to hide the result of the select?
    As "set echo off", "set feedback off", what else I'm missing?

    Thanks!
    Saludos...
    Germán.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In a script you can use SET TERM OFF to suppress all output to the screen while it runs.

  9. #9
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Thanks again, Tony...

    I've just found another thing, but didn't know the "SET TERM" sentence.
    Code:
    column filename new_value spoolfile noprint
    Saludos!

Posting Permissions

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