Re: Launch an agent which create an Excel file

On Thu, 03 Jan 2002 18:50:02 +0100, Sybrand Bakker <oradba_at_sybrandb.demon.nl> wrote:

>On Thu, 3 Jan 2002 14:19:11 +0100, "Hassen AHLAMINE"
><cainmaro_at_hotmail.com> wrote:
>
>>Hi everybody!
>>
>>I'd like to know if it is possible to create an Oracle agent on a server
>>which run an SQL Request in order to gather data from tables an to save it
>>in an xls file or txt file (xls is better)
>>This agent must be ran every week...
>>
>>Thank you for the help you could bring me...
>>
>
>
>The nearest solution is a stored procedure submitted by dbms_job which
>creates a CSV file as output by means of utl_file.
>
>Hth
>
>Sybrand Bakker, Senior Oracle DBA
>
>To reply remove -verwijderdit from my e-mail address

I'm told (haven't verified) that Excel these days can load in an HTML file that's structured to use <table>, <tr>, <td>, etc. tags. That might be a good format to use for the output.

There are various ways to get the server to embed query results in an HTML file. My personal favourite is to write a stored procedure that uses HTP.PRINT to output HTML, then use Perl's LWP::Simple module to make a request for the URL that runs the stored procedure. That way the output can immediately be mangled, stored, mailed, fed to UNIX commands, or all of the above, and the request can be made from the same server or from any other network-connected machine (even one with no DB client software).

John

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/