Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Friday, 15 August 2008

Oracle Append DateTime to spool file

How to append the date/time to an output spool file in SQLPLUS:

SQL>column dttm new_value v_dttm noprint;
The above construct is used to select a value into a user variable

SQL>select to_char(sysdate,'yyyymmddhh24miss') dttm from dual;
Here we see the user variable data being passed in, i.e. the date and time format. Note I have not used spaces, colons etc in between my date and time strings as sometimes these can lead to SP-0332 Spooling Errors.

SQL>spool filename_&v_dttm..log
Note the two fullstops (..) are deliberate

5 comments:

Anonymous said...

Thanks Nazim, just what I needed.

Chirag said...

Highly useful.

sundar_rajan said...

Thanks for the valuable information. Especially for note (Note the two fullstops (..) are deliberate)

Anonymous said...

Excellent, thanks a lot! Cedric

Gandhi Rajakumar said...

Exactly the same thing I wanted. THANKS.