Re: oracle output to excel

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Mon, 6 Feb 2006 20:55:30 -0500
Message-ID: <sYWdnW00qJXbYnreRVn-ug@comcast.com>

"amogh" <amogh.r_at_gmail.com> wrote in message news:wCIFf.30$eu3.105_at_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

good use of mime type.

but no longer necessary if the OP's database is at least 9iR2 -- just use HTML DB (er, Apex, that is).

however, OP was looking to schedule the job -- so neither roll-your-own htp calls or point-and-click HTML DB is an appropriate solution

++ mcs Received on Mon Feb 06 2006 - 19:55:30 CST