dbms_output will achieve nothing when a procedure is invoked by the
WebServer agent. SERVEROUTPUT is a SQL*Plus setting - WebServer does not
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
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.
> 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.
> > Hi Everyone:
> > Using the Webserver PL/SQL agent, it it possible to send the output of a
> > query to a file?