Very Computer

Hi Everyone:

Using the Webserver PL/SQL agent, it it possible to send the output of a
query to a file?

-Dan

--
"When all is said and done, a lot gets said, little gets done"

My e-mail address has been altered to fustrate spammers.
Please replace the "no.spam" with "sd.znet" to reply.

Dan,

The procedure is quite simple after you've done it once. . . You'll have to
use the UTL_FILE package that shipped with the Webserver. Of course, like
all of Oracle's tools, you have to install the package if you didn't do it
during the install.

To write to disk use the 'open' command followed by a 'put_line' ... actual
code from our feedback form is shown below.

It is important that your ORAxx.INI file has been appended to indicate which
directories are allowed access from the Utl_file package...commands shown
below.

Place in INITorcl.ORA (may differ) --> utl_file_dir = C:\dirpath1\dirpath2

Sample Code   -->
****************************************************
      SaveToFile utl_file.file_type;
      FileName Varchar2(40);
      FileLoc Varchar2(40);
   Begin
      FileName := 'File'||to_char(sysdate,'DDHHMISS')||'.txt';   /*Create
name based on date/time */
      FileLoc := 'C:\dirpath1\dirpath2';     /*Directory must have been
previously declared in InitOrcl.ora/*
      SaveToFile := utl_file.fopen(FileLoc,FileName,'a');         /* The 'a'
indicates append /*
       utl_file.put_line(SaveToFile,'FILE CONTENTS....');        /* Insert
variables or loop to build list etc. */
       utl_file.fclose(SaveToFile);
   End;
******************************************************

Cheers.

Dan,

I haven't tried it, but there's no reason that DBMS_OUTPUT shouldn't
work. If turning on SERVEROUTPUT is the problem, that can be done
automatically (via login.sql) for the user session connected to the
PL/SQL agent.

If this doesn't work, email me and I'll try it here.

Ken

dbms_output will achieve nothing when a procedure is invoked by the
WebServer agent. SERVEROUTPUT is a SQL*Plus setting - WebServer does not
use SQL*Plus.

To put query results in a file as you wish, you can use UTL_FILE. You
will have to duplicate the output, i.e. use the htp.xxx procedures AND
also send the same output via the utl_file procedures. If you simply
want to test a procedure that you wish to deply via WebServer, you can
executre the procedure within SQL*Plus, set serveroutput on and then
execute owa_util.showpage. At least one problem I came across trying to
do this is that any references to cgi environment variables (via
owa_util.get_cgi_env, or something like that) cause a "PL/SQL numeric or
value error".

While I'm writing, here's an improvement: instead of duplicating output
using utl_file all over your procedure code, just put a few lines at the
end of the procedure to loop through the htp.htbuf array from 1 to
htp.rows_in, and copy the lines to either a file (using utl_file), or
put the result in a table. This could even be packaged up as a utility
procedure to be called from your application procedures when needed. You
may also want to put this in your general exception handler, to catch
output up to the point of failure.

Note: htp.htbuf is an array of VARCHAR(255), and it holds the result of
all those ridiculous calls to htp.print, htp.bold... htp.rows_in is a
BINARY_INTEGER index for the array. In fact, due to Oracle's appalling
inefficient procedure calling, it is much (like, twice) quicker to stuff
this array directly than to use all those htp/htf procedures/functions.

Brent Smith.

Use the UTL_FILE package.

If you want to know details send mail.

G?ran
--

G?ran Korsgren, ChampionChip AB, Stockholm, Sweden

If replying by mail, please remove x. from address!

If you want to know details send mail.

G?ran
--

G?ran Korsgren, ChampionChip AB, Stockholm, Sweden

If replying by mail, please remove x. from address!