Home » SQL & PL/SQL » Client Tools » report generation (oracle 10g )
report generation [message #414745] Thu, 23 July 2009 06:36 Go to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
Hi,

I have been trying to get report generated from sql*plus in a well formatted way.

I have used,
set echo off
spool c:\temp.xls
select * from temp where rownum<6;
spool off; 


But the above is not giving me the well formated output into excel sheet.

I am unable to attach the excel file here that i pulled as the excel format is not supported here to upload.

So, i will say, my table has some 20 column if i go by spooing via sql * plus its not giving well formated output. How to format that.

Any specific commands please.

I want to do the same via sql * plus

please help me how to get that done.


Regards,
Pointers
Re: report generation [message #414748 is a reply to message #414745] Thu, 23 July 2009 06:40 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What do you mean by "well formated output"?



Re: report generation [message #414753 is a reply to message #414748] Thu, 23 July 2009 06:52 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
Yes..i.e. i would like the output like,

1. all columns in a sinle row (i have 20 columns)
2. data according to the column headings i.e. cells in excel sheet

I would say a simple readable report.

i am getting column headings in multilple rows when i go by sql * plus

Regards,
Pointers.
Re: report generation [message #414754 is a reply to message #414748] Thu, 23 July 2009 06:59 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, perhaps choosing XLS as an extension wasn't the best choice. Excel "thinks" it is an Excel file, but it is not - it is just a text file.

So here it is: let's set column separator to semi-colon and spool result of a SELECT statement into a file
SQL> set colsep ";"
SQL> spool emp.txt
SQL> select * from emp;

     EMPNO;ENAME     ;JOB      ;       MGR;HIREDATE           ;       SAL;
----------;----------;---------;----------;-------------------;----------;
      7369;SMITH     ;CLERK    ;      7902;17.12.1980 00:00:00;      1600;
      7499;ALLEN     ;SALESMAN ;      7698;20.02.1981 00:00:00;      2400;
      7521;WARD      ;SALESMAN ;      7698;22.02.1981 00:00:00;      2050;
      7566;JONES     ;MANAGER  ;      7839;02.04.1981 00:00:00;      3775;
      7654;MARTIN    ;SALESMAN ;      7698;28.09.1981 00:00:00;      2050;
      7698;BLAKE     ;MANAGER  ;      7839;01.05.1981 00:00:00;      3650;
      7782;CLARK     ;MANAGER  ;      7839;09.06.1981 00:00:00;      3250;
      7788;SCOTT     ;ANALYST  ;      7566;09.12.1982 00:00:00;      3800;
      7839;KING      ;PRESIDENT;          ;17.11.1981 00:00:00;      5800;
      7844;TURNER    ;SALESMAN ;      7698;08.09.1981 00:00:00;      2300;
      7876;ADAMS     ;CLERK    ;      7788;12.01.1983 00:00:00;      1900;
      7900;JAMES     ;CLERK    ;      7698;03.12.1981 00:00:00;      1750;
      7902;FORD      ;ANALYST  ;      7566;03.12.1981 00:00:00;      3800;
      7934;MILLER    ;CLERK    ;      7782;23.12.1982 00:00:00;      2100;

14 rows selected.

SQL> spool off;

Now run Excel, go to "Open" and choose "emp.txt". Import Wizard will open, asking you to choose file type - you'll say that it is "Delimited". Delimiter is a semi-colon (so check this checkbox). You may revise column data formats if you want, but "Finish" will display a quick (and dirty) result: every table column will be placed into its own Excel column:

./fa/6578/0/
Re: report generation [message #414755 is a reply to message #414753] Thu, 23 July 2009 07:01 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
In that regard, excel works better when opening HTML files than CSV or text files.

Try an additonal

set markup html on


before the spool
Re: report generation [message #414761 is a reply to message #414753] Thu, 23 July 2009 07:29 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
pointers wrote on Thu, 23 July 2009 13:52

i am getting column headings in multilple rows when i go by sql * plus

Set pagesize to a required value.
Considering the fact that you have been working with Oracle for at least a year now, one might expect you to know this, or to know where to find this.
Re: report generation [message #414811 is a reply to message #414745] Thu, 23 July 2009 10:47 Go to previous message
pointers
Messages: 350
Registered: May 2008
Senior Member
Thomas thanks for the information. That worked me.

Do we have any direct way of creating excel file(formated like using delimiter) for the same thing.

Regards,
Pointers
Previous Topic: SQL Plus - Problem with variable PLUS_DFLT
Next Topic: Oracle ADI - Force Excel 2003
Goto Forum:
  


Current Time: Sat Aug 23 01:25:31 CDT 2014

Total time taken to generate the page: 0.10060 seconds