Spool Date & Timestamp using PL/SQL

Hi Friends,

The other day i was trying to write an automated alert script to monitor the asynchronous requests in Siebel Server or in other words trying to monitor the S_SRM_REQUEST table for any failed async requests.

Well in order to achieve the same, i create a quick PL/SQL Script which spooled the necessary information i required as no out of box solution existed from Siebel.
But wait a minute, the timestamp was not getting captured in the spooled out :-(
It was only spooling the date instead of the entire date & time!

For e.g. if run the below SQL, it will only spool the date



Output was like below :-(:
SYSDATE
---------
07-JUL-10

Quick research on Google & it pointed me that we need to alter our session to set the NLS_DATE_FORMAT before the spool will actually capture the Date & Time...
So based on this recommendation, i revised my PL/SQL Block to:



The end result is,
SYSDATE
--------------------
07-JUL-2010 08:14:19

Yipee! :-D
I posted here, as i thought its a very useful info for everyone :-)
Happy Weekend!

Keywords:
Programming, Siebel Async Request, S_SRM_REQUEST, PL/SQL, NLS_DATE_FORMAT, SPOOL, Oracle, etc.