Re: Webserver Output

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.

Ken Nichols wrote:
>
> 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
>
> Dan Nichols wrote:
> >
> > Hi Everyone:
> >
> > Using the Webserver PL/SQL agent, it it possible to send the output of a
> > query to a file?
> >
  Received on Tue Oct 14 1997 - 00:00:00 CEST