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"

1 comment:

Anonymous said...

Amiable brief and this fill someone in on helped me alot in my college assignement. Thank you on your information.