Showing posts with label spool current date. Show all posts
Showing posts with label spool current date. Show all posts

Oct 1, 2007

Spool with auto file name and current date-time

When I run the scheduled job to do manual cleanup in database - I need to spool those things for any future need. To do so, I have to dynamically generate spool file name to keep track while running the scripts by crontab. I modified my cleanup.sql file to generate spool file name-

#cleanup.sql
col sysdt noprint new_value sysdt
SELECT TO_CHAR(SYSDATE, 'yyyymmdd') sysdt FROM DUAL;
col systm noprint new_value systm
SELECT TO_CHAR(SYSDATE, 'hhmiam') systm FROM DUAL;

SPOOL /u/shawon/manual-cleanup/db-manual-cleanup-&sysdt-&systm..log
....
....

COMMIT;
SPOOL OFF

When this file is called by scheduler, it selects spool file name accordingly - "db-manual-cleanup-20071001-0354am.log"