Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34

    Angry Unanswered: spooling with date appended to filename

    Hi

    I am trying to create a file with the date appended to the filename.

    So far I have tried this

    define dd='a'
    col sysdate new_value dd
    select trunc(sysdate) from dual;

    spo abc&&dd..txt
    @script2.sql
    spo off

    The result is a file named abca.txt

    The funny thing is when I try this with a simpler query it works

    @script1.sql = select * from def where col1 = 123;

    define dd='a'
    col sysdate new_value dd
    select trunc(sysdate) from dual;

    spo abc&&dd..txt
    @script1.sql
    spo off

    The result is a file named abc11-DEC-03.txt

    my other concern is that I would like the date to be formatted (mmddyyyy) so that the file look like this abc12112003.txt

    If anyone could provide any info on this it would be much appreciated

    Thanks

    Hamza

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    are you using UNIX or WINDOWS platform?
    Unix is easy so I will assume you are using WINDOWS.

    (I did not write this)
    PHP Code:
    column whichdate new_value whichday noprint
    select to_char
    (sysdate,'MMDDYYYY'whichdate from dual;

    12:11:54 kod:testspool test_file_&&whichday
    12
    :12:33 kod:testselect sysdate from dual;

    SYSDATE
    ---------
    11-DEC-03

    Elapsed
    00:00:00.00
    12
    :12:51 kod:testspool off
    filename = test_file_12112003.LST
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    The Duck's code looks familiar ... That's the way I handle it ...

    Gregg

  4. #4
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    Originally posted by The_Duck
    are you using UNIX or WINDOWS platform?
    Unix is easy so I will assume you are using WINDOWS.

    (I did not write this)
    PHP Code:
    column whichdate new_value whichday noprint
    select to_char
    (sysdate,'MMDDYYYY'whichdate from dual;

    12:11:54 kod:testspool test_file_&&whichday
    12
    :12:33 kod:testselect sysdate from dual;

    SYSDATE
    ---------
    11-DEC-03

    Elapsed
    00:00:00.00
    12
    :12:51 kod:testspool off
    filename = test_file_12112003.LST

    I was trying this in Windows but I would like to eventually do this in Unix so if you could let me know how to do this in UNIX it would much appreciated

    THanks

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by gbrabham
    The Duck's code looks familiar ... That's the way I handle it ...

    Gregg
    Heh, I definitely did not write it, but it works.

    Do the same for Unix if you like.
    I use the date function in Unix to get timestamps the way I want them.
    This is to get dated files, but you can also use the date function for sql as well.
    example:
    PHP Code:
    > echo "\$ get_data `date +'%m%d%y'`"ftp -v 66.210.81.61
    Connected to 66.210.81.61
    .
    220 Serv-U FTP Server v4.0 for WinSock ready...
    331 User name okayneed password.
    230 User logged inproceed.
    ?
    Invalid command
    get BOS
    -F-121103.TXT
    200 PORT Command successful
    .
    150 Opening ASCII mode data connection for BOS-F-121103.TXT (25465 bytes).
    226 Transfer complete.
    localBOS-F-121103.TXT remoteBOS-F-121103.TXT
    25465 bytes received in 0.51 seconds 
    (48.64 Kbytes/s)
    get HFD-F-121103.TXT
    200 PORT Command successful
    .
    150 Opening ASCII mode data connection for HFD-F-121103.TXT (25169 bytes).
    226 Transfer complete.
    localHFD-F-121103.TXT remoteHFD-F-121103.TXT
    25169 bytes received in 0.52 seconds 
    (47.69 Kbytes/s)
    get YYZ-F-121103.TXT
    200 PORT Command successful
    .
    150 Opening ASCII mode data connection for YYZ-F-121103.TXT (25272 bytes).
    226 Transfer complete.
    localYYZ-F-121103.TXT remoteYYZ-F-121103.TXT
    25272 bytes received in 0.51 seconds 
    (48.59 Kbytes/s)
    get GEG-F-121103.TXT
    200 PORT Command successful
    .
    150 Opening ASCII mode data connection for GEG-F-121103.TXT (25361 bytes).
    226 Transfer complete.
    localGEG-F-121103.TXT remoteGEG-F-121103.TXT
    25361 bytes received in 0.51 seconds 
    (48.74 Kbytes/s)
    quit
    221 Goodbye
    !
    *
    kod1-/export/oracle
    ls
    BOS
    -F-121103.TXT   README.html        sp_37_38.lst       u03
    Disk1              SP_CREATE_ORG
    .sql  test.par           u04
    GEG
    -F-121103.TXT   YYZ-F-121103.TXT   u01                u05
    HFD
    -F-121103.TXT   mbox               u02                u06
    *kod1-/export/oracle

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Whoa!! What is with all the ftp stuff? Not sure where The_Duck is waddling off to, but the sql provided earlier works just fine. SQL is SQL no matter what platform you are on. If you are looking for date functions on UNIX out side of SQL send another reply and I can help.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296

    Thumbs down

    possibly too much information.
    sorry.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    I think I figured out what was wrong with my script

    original script:

    define dd='a'
    col sysdate new_value dd
    select trunc(sysdate) from dual;

    spo abc&&dd..txt
    @script2.sql
    spo off

    The result is a file named abca.txt

    revised script:

    define dd='a'
    col to_char(sysdate,'mmddyyyy') new_value dd
    select to_char(sysdate,'mmddyyyy') from dual;

    spo abc&&dd..txt
    @script2.sql
    spo off

    The result is a file named abc12122003.txt

    Thanks

Posting Permissions

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