Insert Date for Log

[cwsstins]

I've got a .sql script running from a UNIX ksh shell script. Part of the .sql includes a SPOOL command to a log file. I'd like to include the date/time in the log file so it is a little more useful.

Here's my code:

DEFINE LOG_DIR=&1
SPOOL &&LOG_DIR/file.log
ALTER SYSTEM FLUSH SHARED_POOL
@@Date
/
TRUNCATE TABLE tblname
/
SPOOL OFF
EXIT

Please advise on how I can add the date/time to the log file being generated.

Also, I keep getting this error when I run the .sql from the UNIX shell script...does this mean the SQL login I'm using to run this doesn't have access to run that command?:

ALTER SYSTEM FLUSH SHARED_POOL
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

[cwsstins]

Well, I've got this...

DEFINE LOG_DIR=&1
SPOOL &&LOG_DIR/file.log
ALTER SYSTEM FLUSH SHARED_POOL
@@Date
/
TRUNCATE TABLE tblname;
[b]select sysdate from dual;[/b]
/
SPOOL OFF
EXIT

and it works, but it prints the date twice, which I don't understand. And I don't know how to find the date...

 

[taupirho]

Try this.

column dcol new_value mydate noprint
select 'mylog_' || to_char(sysdate,'YYYYMMDD') || '.log' dcol from dual;

spool &mydate
etc ...

 

[cwsstins]

Thanks for the reply. I actually ended up using this:

select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;