Attractive Reports from SQLPLUS


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 HTML ON SPOOL ON
SQL &gt    SPOOL C:\test.html
SQL &gt    select * from employee; ---- assuming that employee is an existing table.
SQL &gt    SPOOL OFF
SQL &gt    SET MARKUP HTML OFF
SQL > EXIT
Above command “SET MARKUP HTML ON” create the result in html format. And you get the output with pre-formatted output (without any effort), see the snapshot below:

Amazingly, if we change the extension of filename from “html” to “xls” in above commands, the pre-formatted excel report is generated. The file contains the same content and looks brilliant in excel (see snapshot below) J


[This was posted on my official site on 11/1/2010 1:09 AM]

Comments

Post a Comment

Popular posts from this blog

Sequence.nextval can really deceive you !!

Yes, they really can. Yesterday, I noticed that one of the sequence number (pol_no_seq) in our IH application was giving different set of results when accessed (pol_no_seq.nextval) from two different machines. When I shared this observation with colleagues, the feedback was something like "It does not make sense" and frankly speaking, even I thought the same :-) But, how could I ignore what I actually saw with my own eyes ? I was really puzzled. Given below is the explanation of what and why it happenned, based on my findings from some forums: Definition of pol_no_seq : CREATED 25-JAN-07 LAST_DDL_TIME 25-JAN-07 SEQUENCE_OWNER INSURANCE SEQUENCE_NAME POL_NO_SEQ MIN_VALUE 1 MAX_VALUE 1000000000000000000000000000 INCREMENT_BY 1 CYCLE_FLAG N ORDER_FLAG N CACHE_SIZE 200 LAST_NUMBER 2802765 Results : Machine A : pol_no_seq.nextval->28000146 Machine A : pol_no_seq.nextval->28000147 Machine B : pol_no_seq.nextval->28000412 Machine ...
Read more

Traps in Decision Making

Decision making is an important activity, not only for executives but for everyone in the world. In our day to day life, we make lot of decisions. Starting from choosing between chocolate and strawberry to life and death, we make tons of quick decisions unconsciously. Some of these decisions may not be so important but some can be really vital and may result in disaster. We choose actions from opinions via mental processes which are influenced by biases, reason, emotions and memories.  While pursuing AMDA Silver certification, I came across a white paper on Decision Making which explained lot of interesting hidden traps that a decision maker may ignore while decision making. In this blog, I would like to share some of those common traps which we should avoid while making a decision. Anchoring Trap : When considering a decision, mind gives disproportionate weight to the first information it receives. You can try it!! If I ask you two questions: a)  ...
Read more