Using sysdate in filename

[ssmith001]

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)?

[gbrabham]

set your own format ...

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


HTH
Gregg

[ssmith001]

It works! Thanks Gregg

[binoyshil]

thanx a lot gregg

[binoyshil]

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

[TimHall]

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...

[WilliamR]

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.

[hrishy]

Hi William


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

How will it break.

regards
hrishy

[WilliamR]

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.

[Cookies]

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:

  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