OraFAQ Forum: Client Tools » report generation

Home » Other » Client Tools » report generation (oracle 10g )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

report generation [message #414745]

Thu, 23 July 2009 06:36

pointers
Messages: 451
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,

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: report generation [message #414748 is a reply to message #414745]

Thu, 23 July 2009 06:40

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

What do you mean by "well formated output"?

Report message to a moderator

Re: report generation [message #414753 is a reply to message #414748]

Thu, 23 July 2009 06:52

pointers
Messages: 451
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.

Report message to a moderator

Re: report generation [message #414754 is a reply to message #414748]

Thu, 23 July 2009 06:59

Littlefoot
Messages: 21824
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

Code: [Select all] [Show/ hide]

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:

/forum/fa/6578/0/

Attachment: excel_spool.PNG
(Size: 18.95KB, Downloaded 2173 times)

Report message to a moderator

Re: report generation [message #414755 is a reply to message #414753]

Thu, 23 July 2009 07:01

ThomasG
Messages: 3212
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

Code: [Select all] [Show/ hide]

set markup html on

before the spool

Report message to a moderator

Re: report generation [message #414761 is a reply to message #414753]

Thu, 23 July 2009 07:29

Frank
Messages: 7901
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.

Report message to a moderator

Re: report generation [message #414811 is a reply to message #414745]

Thu, 23 July 2009 10:47

pointers
Messages: 451
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

Report message to a moderator

Previous Topic:	SQL Plus - Problem with variable PLUS_DFLT
Next Topic:	Oracle ADI - Force Excel 2003

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 30 10:36:04 CDT 2025