Re: oracle output to excel

From: amogh <amogh.r_at_gmail.com>
Date: Mon, 06 Feb 2006 19:11:26 +0530
Message-ID: <wCIFf.30$eu3.105@news.oracle.com>

Mark C. Stock wrote:
> "amogh" <amogh.r_at_gmail.com> wrote in message
> news:C9EFf.8$eu3.110_at_news.oracle.com...
>

>>Murtuja wrote:
>>
>>>I want daily report in Excel format.and this excel file should be
>>>mailed to client.I want autoamtic process for that just like cron job.
>>>
>>>I am using Oracle 9i on Linux
>>>
>>
>>The owa package has methods to dump data in a .xls format.
>>You would have to make use of the htp packages to print
>>the data for this.
>>
>>Rgds.
>>Amogh

>
>
> the owa package itself contains no such function or procedure
>
> SQL> describe owa
> FUNCTION GET_LINE RETURNS VARCHAR2
> IROWS NUMBER(38) OUT
>
> PROCEDURE GET_PAGE
> THEPAGE TABLE OF VARCHAR2(256) OUT
> IROWS NUMBER(38) IN/OUT
>
> PROCEDURE GET_PAGE_CHARSET_CONVERT
> THEPAGE TABLE OF VARCHAR2(256) OUT
> IROWS NUMBER(38) IN/OUT
> CHARSET VARCHAR2 IN
>
> PROCEDURE GET_PAGE_RAW
> THEPAGE TABLE OF RAW(256) OUT
> IROWS NUMBER(38) IN/OUT
>
> FUNCTION INITIALIZE RETURNS NUMBER(38)
>
> PROCEDURE INIT_CGI_ENV
> PARAM_VAL TABLE OF VARCHAR2(32000) IN
>
> PROCEDURE INIT_CGI_ENV
> NUM_PARAMS NUMBER IN
> PARAM_NAME TABLE OF VARCHAR2(32000) IN
> PARAM_VAL TABLE OF VARCHAR2(32000) IN
>
> PROCEDURE SET_PASSWORD
> PWD VARCHAR2 IN
>
> PROCEDURE SET_TRANSFER_MODE
> TMODE VARCHAR2 IN
>
> PROCEDURE SET_USER_ID
> USR VARCHAR2 IN
>
>
> and the htp package would only be used if output to a browswer via mod_plsql
> is required/configured
>
> ++ mcs
>
>
>

That was a typo. My mistake. It's the owa_util package.

This code snip shows how to do it:

procedure sample_proc
is
begin
owa_util.mime_header('application/vnd.ms-excel',FALSE); htp.p('Content-disposition: attachment;filename=sample_report.xls'); owa_util.http_header_close;

htp.htmlopen;
htp.bodyopen;
htp.tableopen;

htp.tablerowopen;
htp.tableheader('Column1' ,cnowrap => 'Y' );
htp.tableheader('Column2' ,cnowrap => 'Y');

htp.tableheader('Column3' ,cnowrap => 'Y' ); htp.tablerowclose;

for arec in(
SELECT col1,col2,col3 FROM your_tab
)

loop

htp.tablerowopen;
htp.tabledata(arec.col1,calign=>'center');
htp.tabledata(arec.col2,calign=>'center');
htp.tabledata(arec.col3,calign=>'center');
htp.tablerowclose;

end loop;

htp.bodyclose;
htp.htmlclose;
end;

Excecuting this guy from a browser should save the output as an excel file.

Rgds.
Amogh Received on Mon Feb 06 2006 - 07:41:26 CST