Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2003
    Location
    Saint Lucia
    Posts
    14

    Exclamation Unanswered: Spool To Text File

    Hi There,
    I am preparing a script that will run every morning at 1am on my ora db.
    However i have a spool statement for which i want the text file to be created with the naming format of August-16-2003. In order words whatever day that the script is executed, the spooled file must reflect that based upon the name. Does anyone here have any suggestion?

    Thanks Much

  2. #2
    Join Date
    Jul 2003
    Posts
    4
    you may try this

    define dd='a'
    col sysdate new_value dd
    select sysdate from dual;
    spo &&dd.spl

    @yourscript

    spo off

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    column whichdate new_value whichday noprint

    select trunc(sysdate) whichdate from dual;

    spool c:\File_&&whichday..txt


    Hope this helps
    Gregg

  4. #4
    Join Date
    Aug 2003
    Location
    Saint Lucia
    Posts
    14

    Exclamation

    Hi Gregg,
    I tried your script, it works but i still have a few concerns...
    This script is supposed to be automated, however i'm being prompted to enter a 'whichday' value...

    Also the file extension is .LST and not text. can you help? I want the format to be text...


    Thank you much.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If you run the above 3 lines in SQLPlus, you should not be asked for
    a date... Whichday is replaced by the value of Whichdate.

    As for LST .... change the spool file to whatever extension you want...

    If you want to format a report of some kind, use SQLPlus formatting
    with COLUMN commands... If you want to have a comma delimited file,
    you can do that and turn off headings, etc ...

    Gregg

  6. #6
    Join Date
    Aug 2003
    Location
    Saint Lucia
    Posts
    14
    Hi,
    This is the error i'm getting when i try line by line...

    SQL> spool c:\rep_&&whichday.txt
    SP2-0333: Illegal spool file name: "c:\rep_25/09/2003 00:00:00txt" (bad character: ' ')

    Any ideas?

    Cheers

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You need to do a to_char on the date as well as a trunc ...
    It doesn't like "\" nor a space for the filename.

    Gregg

  8. #8
    Join Date
    Aug 2003
    Location
    Saint Lucia
    Posts
    14
    I've found another way of doing it by modifying the NLS_DATE_FORMAT in registry of the machine that I'm running the report from. Would this have any implications? It works fine this way.

    Anyway, i'm pretty new to oracle, but with regards to your last response, can you give me an example of the structure of the script?

    Cheers

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    This is a script that is run every night at 1 of my client's site.

    column whichdate new_value whichday noprint

    select to_char(trunc(sysdate),'dd-mon-yy') whichdate from dual;

    set verify on
    set echo on
    set termout on

    execute Process_x_File.Do_Daily_File('&whichday');
    set linesize 120
    set space 0
    set pagesize 0
    set feedback off
    set verify off
    set termout off
    set heading off
    set serveroutput off
    set echo off
    set show off
    set trim off
    set concat on
    set linesize 60

    column DAY_NAME new_value file_day noprint

    select trunc(SYSDATE) DAY_NAME FROM DUAL;

    host del c:\1\x_file*.txt
    spool c:\1\x_File_&&whichday..txt

    select
    to_char(REPORTING_DATE,'YYYYMMDD')||','||LOCATION_ IDENTIFIER||','||PART_ID||','||UNIT_OF_MEASURE||', '||QTY_ON_HAND||','
    from parts
    where reporting_date = upper('&WHICHDAY') and qty_on_hand > 0
    order by location_identifier, catalog_id;

    spool off
    .......


    HTH
    Gregg

  10. #10
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21
    Hi Greg,

    I'm trying to run a sql script every month, with the output file containing the date e.g. file_02-29-2004.bas. I saw that you have done something similar to this. I tried your script but I was getting an error, that this line is invalid "column whichdate new_value whichday noprint".

    Your assistance will be highly appreciated



    Thanks
    It pays to share!

  11. #11
    Join Date
    Aug 2003
    Location
    Saint Lucia
    Posts
    14
    Hi Daymauler,
    You can use the following:
    1. First use the set command to set the NLS_DATE_FORMAT that you want to use.
    2. column whichdate new_value whichday noprint
    3. select trunc(sysdate) whichdate from dual;
    4. spool c:\Filename_&&whichday..bas;
    5. [Insert your script here]
    6. spool off;

    If you encounter any problems, please let me know

    Cheers

  12. #12
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21
    Thanks a bunch Greg! It worked fine.
    It pays to share!

  13. #13
    Join Date
    Aug 2003
    Location
    Saint Lucia
    Posts
    14
    Hi,
    I'm not greg. Read my post carefully

    Thanks

  14. #14
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    21
    Big apologies ramdatts
    It pays to share!

Posting Permissions

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