Posts

Showing posts from November, 2010

Attractive Reports from SQLPLUS

Image
Hi Everyone, On last Friday, I finished working on automating the generation and mailing of a report. Got to learn few interesting way of generating reports directly from SQLPLUS without any extra effort and would like to share that with you. SPOOL ON/OFF is a common functionality in SQLPLUS to write the output of query into a file. Following lines will send the output to C:\test.txt file. SQL> SPOOL C:\test.txt SQL> select * from employee; ---- assuming that employee is an existing table. SQL> SPOOL OFF Now, we might be interested in creating output report in excel format. Simplest way will be: SQL> SET COLSEP ‘,’ SQL> SPOOL C:\test.csv    --- filename with “.csv” as extension SQL> select * from employee; ---- assuming that employee is an existing table. SQL> SPOOL OFF Open test.csv in excel and you are there. But, what if we need output in HTML format????? ………………Well, there is a way to do this as well in SQLPLUS. See below: SQL > SET MARKUP HT...
Post a Comment
Read more

Scheduling E-mailer

Image
While authoring a script recently on Windows XP, which was to be executed by windows scheduler, I used a tool called “Blat” (already in use in one of the application in our group) which helps in sending email. On command line, the syntax would be like the one below:   C:\> blat –to <To Email Adress> -from <From Email Adress> -server <SMTP Server Name> -body “<body text>” –subject “<Subject>”   For further details, refer to http://www.blat.net   On Windows XP, I had to write a ".bat" file and schedule it in Scheduled Tasks option. However, on Windows 7, there are in-built option for scheduling of sending emails.   1. Go to Control Panel -> Scheduled Task -> Create Basic Task 2. Specify the Trigger i.e. when to run the task (Date, time, recurrence etc.) 3. In Action, there are 3 options:          Start a Program    // Will execute any program  ...
Post a Comment
Read more