Home » SQL & PL/SQL » SQL & PL/SQL » How can I store sql query result in different tabs of excel
icon6.gif  How can I store sql query result in different tabs of excel [message #206522] Thu, 30 November 2006 07:44 Go to next message
adalal
Messages: 2
Registered: November 2006
Junior Member
No Message Body
Search for OWA_SYLK on Google or AskTom [message #206526 is a reply to message #206522] Thu, 30 November 2006 07:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Two can play at the 'No message body' game.
Re: How can I store sql query result in different tabs of excel [message #206534 is a reply to message #206522] Thu, 30 November 2006 08:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Do not report your own messages to the moderator.
It never results in your message getting answered sooner.
Re: How can I store sql query result in different tabs of excel [message #206551 is a reply to message #206522] Thu, 30 November 2006 08:51 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
If you want our help, then take the time to write out a valid message. To create a request without background information and without even a message body is rude. As for your answer, the only way that I ever got this to work directly into an excel spreadsheet was by using oracle forms and using OLE.
Re: How can I store sql query result in different tabs of excel [message #206560 is a reply to message #206551] Thu, 30 November 2006 09:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I use PERL and Spreadsheet::WriteExcel for writing Excel files.

Which has the bonus that you can batch it easily and it runs on practically anything that runs PERL.

Example :

http://homepage.eircom.net/~jmcnamara/perl/WriteExcel.html
Re: How can I store sql query result in different tabs of excel [message #206600 is a reply to message #206522] Thu, 30 November 2006 13:29 Go to previous messageGo to next message
Mike Bentley
Messages: 8
Registered: September 2006
Junior Member
I use this in SQL*PLUS

set heading on
SET LINESIZE 500 VERIFY OFF FEEDBACK OFF
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF 
spool c:\oracle\ora92\bin\smm_studyact_errors.xls'


followed by the SQL select. The columns returned are automatically in seperate columns on the spreadsheet.

Mike
Re: How can I store sql query result in different tabs of excel [message #206607 is a reply to message #206522] Thu, 30 November 2006 15:56 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Mike, that will work for a single sheet of the spreadsheet, but the user was (unless I am mistaken) asking how to populate multiple sheets in an excel spreadsheet automatically.
icon6.gif  Re: How can I store sql query result in different tabs of excel [message #206677 is a reply to message #206560] Fri, 01 December 2006 01:19 Go to previous messageGo to next message
adalal
Messages: 2
Registered: November 2006
Junior Member
Can you please tell me instead of Pearl can we use Unix shell script to do the same, if so please guide me.

Thanks. Razz
Re: How can I store sql query result in different tabs of excel [message #206739 is a reply to message #206522] Fri, 01 December 2006 05:32 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
SQL> set heading on
SQL> SET LINESIZE 500 VERIFY OFF FEEDBACK OFF
SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
SP2-0158: unknown SET option "MARKUP"

gives an error
Re: How can I store sql query result in different tabs of excel [message #206765 is a reply to message #206739] Fri, 01 December 2006 07:02 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This error might be Oracle version dependant. What is your Oracle version?
Re: How can I store sql query result in different tabs of excel [message #282032 is a reply to message #206522] Tue, 20 November 2007 08:32 Go to previous messageGo to next message
torlewski
Messages: 3
Registered: November 2007
Junior Member
Hi guys,

Please help me.

HOw can I copy the contents to Excel if I have more rows than 65536 ?

I use PL/SQL Developer.
(I'd like the sql query result will be in different tabs of excel, if it is possible...)

regards,
Torlewski
Re: How can I store sql query result in different tabs of excel [message #282037 is a reply to message #282032] Tue, 20 November 2007 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is an Excel question, post it in a Microsoft forum.

Regards
Michel
Re: How can I store sql query result in different tabs of excel [message #282040 is a reply to message #206522] Tue, 20 November 2007 09:28 Go to previous messageGo to next message
torlewski
Messages: 3
Registered: November 2007
Junior Member
Hi Michel,

I redefine my question.

I use Oracle 9 with PL/SQL Developer.

I have a select, and the number of the query's result more than 65536 and I'd like to copy all of the rows to excel, but unfortunatelly one sheet in excel 2003 may includes max. 65536 rows.

So, my question is, have U ( or somebody) ever tried copy the query's result like this to excel.
(or some idea?)

regards,
Torlewski
Re: How can I store sql query result in different tabs of excel [message #282046 is a reply to message #282040] Tue, 20 November 2007 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I said: if you can't record more than 64K it is an Excel problem not an Oracle one.
What do you expect from us?

Regards
Michel
Re: How can I store sql query result in different tabs of excel [message #282065 is a reply to message #206522] Tue, 20 November 2007 14:01 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Why does it have to be loaded into excel? It is typically not used for very large data dumps.

However, if you really need to then use utl_file to open multiple flat files, each one holding only 65536 lines. Then you would open up multiple sheets in excel, importing each flat file into a different sheet. In my opinion a total waste of effort.

[Updated on: Tue, 20 November 2007 14:46]

Report message to a moderator

Re: How can I store sql query result in different tabs of excel [message #282282 is a reply to message #206522] Wed, 21 November 2007 06:41 Go to previous message
torlewski
Messages: 3
Registered: November 2007
Junior Member
Michel:

Yeah, unfortunatelly, You are Right.

But, You Know, I hoped You have had a problem like this already, and You have some idea to solve this problem, or you have some tricky sql script, or whatever.

You never Know.

Anyway, Thanks for your help.

Bill:

Thanks for your help too.

I'll try your advice, and I agree with you. It's totally "stupid" thing, but, You Know, what can I do?

Regards,
Torlewski


Previous Topic: error during inserting(ORA-01403: no data found)
Next Topic: SQL query for table cloumn name
Goto Forum:
  


Current Time: Sat Dec 03 20:22:04 CST 2016

Total time taken to generate the page: 0.15927 seconds