Home » SQL & PL/SQL » SQL & PL/SQL » Excel generation from oralce (Oracle 9i)
icon6.gif  Excel generation from oralce [message #425814] Mon, 12 October 2009 05:59 Go to next message
Messages: 17
Registered: June 2009
Junior Member

I need to generate an output of sql script in excel format at users system. How to do that.

I have a script that uses utl.file command but the file can be generated in OS directory of Database only.
Re: Excel generation from oralce [message #425819 is a reply to message #425814] Mon, 12 October 2009 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just search for excel, here or on Google and you find that many and many have already asked the same thing.

Re: Excel generation from oralce [message #425826 is a reply to message #425814] Mon, 12 October 2009 07:12 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
There are of course several issues where:

1) Define what you mean by Excel format? does simple CSV do it for you? Or do you think you need something complicated like a true XLS file?

2) Define what you mean by "at the user's system". By this do you mean the file must end up in a directory accessible by the user? Or do you mean the file must be directly written to a user's directory? If so then why?

3) Define the process you wish to use. I sometimes wonder how today's IT ever gets anything done. We never seem to do any planning or even basic design these days. For example, do you have a process diagram showing what needs to happen, or even a simple bullet list describing how the process you intend to create should work? Here is an exmaple:

a. batch job logs into sqlplus on the database server
b. sqlplus set commands are issued to make environment ready for CSV output
c. spool command is issued that opens file local to the database server in a secure directory
d. select statement is executed that generates CSV output for the data
e. spool off is issued closing the file
f. batch job exits sqlplus with good return code
g. return code is checked if good proceed if not dump error and exit.
h. ftp command pushes file to directory accessible by user, overwrite is allowed
i. ftp command pushes same file to an archive directory
j. job reports successful completion

You see, if you have plan, many of your questions are answered already. Of course there are many other concerns you could address, for example, what file naming strategy will you use? Will dates be in your file names? Will data content be in your file names? Do you care about control records showing record counts and hash sums?

But you get the idea. You need at least the beginings of a plan, then go from there. Where is your plan?

Previous Topic: Need help with SQL query (merged)
Next Topic: running procs concurrently
Goto Forum:

Current Time: Fri Oct 21 22:46:01 CDT 2016

Total time taken to generate the page: 0.11962 seconds