Friday, July 09, 2010

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 :-(:

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,
07-JUL-2010 08:14:19

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

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

1 comment:

Anonymous said...

This may be an old post but you, good sir, are a genius. I was trying to convert the date column in so many ways until I found this blog.