Using sysdate in filename
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Using sysdate in filename

Hybrid View

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    Using sysdate in filename

    Is there a way, using the SPOOL command, to assign the system date to a file name.

    For example, instead of SPOOL c:\data\ord.dat, I want to spool to a file named: ord.20031106083042 (where the extension is YYYYMMDDHHMMSS)?

  2. #2
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    set your own format ...

    column whichdate new_value whichday noprint
    select trunc(sysdate) whichdate from dual;
    spool c:\...\File_&&whichday..txt


    HTH
    Gregg

  3. #3
    Join Date
    Feb 2006
    Posts
    86

    creating spool file at server for each client login to SQL

    column whichdate new_value whichday noprint
    select trunc(sysdate) whichdate from dual;
    spool c:\...\File_&&whichday..txt

    thanx greg for ur help

    can i create a spool file ,in same way, at server end for each client login to SQL.
    with following details in it :
    computer name or IP of that client,
    session time, &
    user name.

    thanx

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    You can do that with Oracle's internal auditing:

    http://www.oracle-base.com/articles/8i/Auditing.php

    You can have an audit trail in the DB, or on the filesystem. It will be alot quicker than manually coding it for yourself. In 10g Release 2, you can product XML output for the audit trail.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    I would also advise giving the date expression a format mask like the one you mentioned so you know what you're getting. For example if the default format ever happens to be "DD/MM/YYYY" the script would break on Unix.

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi William


    Can you please explain what happens when the date format is "DD/MM/YYYY" on unix.

    How will it break.

    regards
    hrishy

  7. #7
    Join Date
    Oct 2002
    Posts
    182
    Quote Originally Posted by binoyshil
    column whichdate new_value whichday noprint
    can i create a spool file ,in same way, at server end for each client login to SQL.
    with following details in it :
    computer name or IP of that client,
    session time, &
    user name.
    You can use sys_context to get almost any kind of information for
    your spool file name:
    PHP Code:
      1  select
      2  sys_context
    ('userenv','db_name'database_name,
      
    3  sys_context('userenv','host'host_name
      4
    from dual
    SQL
    > /

    DATABASE_NAME   HOST_NAME
    --------------- -------------------------------------
    db01            MYHOSTNAME 
    - Cookies

  8. #8
    Join Date
    Aug 2003
    Posts
    100
    It works! Thanks Gregg

  9. #9
    Join Date
    Feb 2006
    Posts
    86
    thanx a lot gregg

  10. #10
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Code:
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    
    SQL> def today="14/02/2006"
    SQL> spool myfile&today..log
    SP2-0332: Cannot create spool file.
    SQL> prompt  myfile&today..log
    myfile14/02/2006.log
    SQL>
    SQL> spool myfile.log
    SQL> spool
    currently spooling to myfile.log
    SQL> spool off
    SQL>
    I guess there is no subdirectory "myfile14/02" to put "2006.log" into.

Posting Permissions

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


Click Here to Expand Forum to Full Width