by > > >Dan Nichols wrote:
> > >> Hi Everyone:
> > >> Using the Webserver PL/SQL agent, it it possible to send the outpu=
t of a
> > >> query to a file?
> With some slight modifications of the OWA_UTIL package you can use it
> to produce files in Excel-format and (depending on browser I assume)
> even have them launch Excel automaticly - the users love it!
> If you want to know details send mail.
> G=F6ran
I got so many emails so I think its better I post my solution to the news=
group:
Basicly, the trick is to start the 'page' with the appropriate mime_heade=
r
(see the example
in the end of this mail), and to change the table...-functions in the
OWA_UTIL-package to
output the data in the way you like to present it.
In the declaration file PUBUTIL.SQL (or a renamed copy) where the followi=
ng
paragraph is:
-----------------------------------------
/* TABLE_TYPE constants */
HTML_TABLE constant integer :=3D 1;
PRE_TABLE constant integer :=3D 2;
-----------------------------------------
add the constant:
-----------------------------------------
EXCEL_TABLE constant integer :=3D 3;
-----------------------------------------
or call it something else if you like.
In the procedure-file PRIVUTIL.SQL (or a renamed copy) change the procedu=
res
from tableOpen to tableClose to the following:
(as you can see, in a lot of the procedures, nothing is to be done if
ntable_type is EXCEL_TABLE)
(I know I could have written 'if (ntable_type =3D EXCEL_TABLE) then...' =
in the
code to make it
more readable, maybe I do some day ...)
-------------------------------------------------------------------------=
-
procedure tableOpen(cattributes in varchar2 DEFAULT NULL,
ntable_type in integer DEFAULT HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableOpen(NULL, NULL, NULL, NULL, cattributes);
elsif (ntable_type =3D PRE_TABLE) then
if (cattributes is not null)
then
table_border :=3D '|';
else
table_border :=3D ' ';
end if;
htp.print('<PRE>');
end if;
end;
procedure tableCaption(ccaption in varchar2,
calign in varchar2 DEFAULT 'CENTER',
ntable_type in integer DEFAULT HTML_TABLE) is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableCaption(ccaption, calign);
elsif (ntable_type =3D PRE_TABLE) then
htp.print(ccaption);
end if;
end;
procedure tableHeaderRowOpen(crowstring in out varchar2,
ntable_type in integer DEFAULT
HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableRowOpen;
elsif (ntable_type =3D PRE_TABLE) then
crowstring :=3D table_border;
else
crowstring :=3D '';
end if;
end;
procedure tableHeaderRowOpen(crowstring in out varchar2,
ntable_width out integer,
ntable_type in integer DEFAULT
HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableRowOpen;
elsif (ntable_type =3D PRE_TABLE) then
ntable_width :=3D 1;
crowstring :=3D table_border;
else
crowstring :=3D '';
end if;
end;
procedure tableHeader(ccolumn_name in varchar2,
ncolumn_size in integer,
calign in varchar2 DEFAULT 'CENTER',
crowstring in out varchar2,
ntable_type in integer DEFAULT HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableHeader(ccolumn_name);
elsif (ntable_type =3D PRE_TABLE) then
crowstring :=3D crowstring||align(ccolumn_name,ncolumn_size,cali=
gn);
else
crowstring :=3D crowstring||ccolumn_name||' '; /* append a tab */
end if;
end;
procedure tableHeader(ccolumn_name in varchar2,
ncolumn_size in integer,
calign in varchar2 DEFAULT 'CENTER',
crowstring in out varchar2,
ntable_width in out integer,
ntable_type in integer DEFAULT HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableHeader(ccolumn_name);
elsif (ntable_type =3D PRE_TABLE) then
ntable_width :=3D ntable_width+ncolumn_size+3;
crowstring :=3D crowstring||align(ccolumn_name,ncolumn_size,cali=
gn);
else
crowstring :=3D crowstring||ccolumn_name||' '; /* append a tab */
end if;
end;
procedure tableHeaderRowClose(crowstring in out varchar2,
ntable_type in integer DEFAULT
HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableRowClose;
else
htp.print(crowstring);
end if;
end;
procedure tableHeaderRowClose(crowstring in out varchar2,
ntable_width in integer,
ntable_type in integer DEFAULT
HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableRowClose;
elsif (ntable_type =3D PRE_TABLE) then
if (table_border =3D '|')
then
htp.print(rpad('-',ntable_width,'-'));
htp.print(crowstring);
htp.print(rpad('-',ntable_width,'-'));
else
htp.print(' ');
htp.print(crowstring);
htp.print(' ');
end if;
else
htp.print(crowstring);
end if;
end;
procedure tableRowOpen(crowstring in out varchar2,
ntable_type in integer DEFAULT HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableRowOpen;
elsif (ntable_type =3D PRE_TABLE) then
crowstring :=3D table_border;
else
crowstring :=3D '';
end if;
end;
procedure tableData(cdata in varchar2,
ncolumn_size in integer,
calign in varchar2 DEFAULT 'LEFT',
crowstring in out varchar2,
ntable_type in integer DEFAULT HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableData(cdata, calign);
elsif (ntable_type =3D PRE_TABLE) then
crowstring :=3D crowstring||align(translate(cdata,NL_CHAR,' '),
ncolumn_size, calign);=
else
crowstring :=3D crowstring||cdata||' '; /* append a tab */
end if;
end;
procedure tableNoData(calign in varchar2 DEFAULT 'LEFT',
crowstring in out varchar2,
nnum_cols in integer,
ntable_width in integer,
ntable_type in integer DEFAULT HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableData('No data found', ccolspan=3D>nnum_cols);
elsif (ntable_type =3D PRE_TABLE) then
crowstring :=3D crowstring||align('No data
found',ntable_width-4,calign);
else
crowstring :=3D crowstring||'No data found ';
end if;
end;
procedure tableRowClose(crowstring in out varchar2,
ntable_type in integer DEFAULT HTML_TABLE)=
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableRowClose;
else
htp.print(crowstring);
end if;
end;
procedure tableClose(ntable_type in integer DEFAULT HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableClose;
elsif (ntable_type =3D PRE_TABLE) then
htp.print('</PRE>');
end if;
end;
procedure tableClose(ntable_width in integer,
ntable_type in integer DEFAULT HTML_TABLE)
is
begin
if (ntable_type =3D HTML_TABLE)
then
htp.tableClose;
elsif (ntable_type =3D PRE_TABLE) then
if (table_border =3D '|')
then
htp.print(rpad('-',ntable_width,'-'));
else
htp.print(' ');
end if;
htp.print('</PRE>');
end if;
end;
-------------------------------------------------------------------------=
-
And finally in the calling procedure (in this example the user had
the choice to push a button 'Show in Excel' or 'Show in Browser',
and the modified packaged is named OWA_UTIL_MOD):
------------------------------------------------------
if (display =3D 'Show in Excel') then
ttype :=3D OWA_UTIL_MOD.EXCEL_TABLE;
owa_util_mod.mime_header('application/excel');
else
ttype :=3D OWA_UTIL_MOD.HTML_TABLE;
htp.htmlOpen;
end if;
--------------------------------------------------------
where you use the ttype-variable as argument whereever a Tabletype-argume=
nt
is needed.
Hope this works for you!
G=F6ran