how to e-mail a report

if the resulting sheet will be 32K or less you can use utl_mail

UTL_MAIL.SEND_ATTACH_VARCHAR2

or

UTL_MAIL.SEND_ATTACH_RAW

Can I directly write this code at the end of the existing procedure or should I create a separate procedure and include the select query which creates the output and write the above code also in the existing procedure.

Please suggest!

it's just one procedure call.  I would just add that one line to whatever code you have that constructs the  string/raw spreadsheet.

Actually, the size of the file is close to  350KB. Does the UTL_MAIL still works ?

no,  utl_mail can only handle 32K in a single attachment

is your data text or binary?

If this is the case, please suggest how I can e-mail the report in Oracle

is the data text or binary?

It is not binary, it generates multiple columns with the datatypes Varchar2 and number.

"multiple columns"  ?

but you are creating a spreadsheet.  What is the type of the final spreadsheet object?

Is it in a clob, a blob, a long, a long raw?

Basically am writing a select statement which is joining multiple tables and fetching the data with the multiple columns which has the datatype number or varchar2.   I  am saving the data whatever I get from this statement as .xls file.  I want to e-mail this file and I am not sure if it is a clob, blob or long or longraw.

Please suggest !

how are you creating the "xls" file?  That's what you want to email,  so that's the important part.

oracle doesn't have a native "create excel format file"  option.  So, whatever you are doing to create that, when you are done you either have a physical file somewhere on your database server, or you have created a blob/long raw (it can't be text)  I can show you how to send either of those.

If you don't actually have such a thing created, you only have the query result sets, then you will need to either, find something that can read your result set and create excel files,  or NOT use .xls and use something else like csv (which excel can read)

I have a physical file in the format .xls saved on my desktop. Can we use this file or Do I have to save the file in .csv format ?

wow! how are you getting data from the server to a file on your desktop?

 I am not sure if you get my statement.  When I run the select query, am saving the output in .xls format on my desktop. That is what I meant..

oh, so you are using some special tool that saves your result sets locally in a file.
something like Toad?

if you want an automated way to do that, Oracle can't do that for you

there are alternatives, such as server side csv generation which can be emailed

Can you please provide me an example ?

Here is the example. We use this for similar requirement as yours.

test.sh
------

#!/bin/ksh
ORACLE_HOME=/opt/oracle/product/10.2
export ORACLE_HOME

$ORACLE_HOME/bin/sqlplus /nolog << !

connect <user>/<pass>@connString
@/home/user/reports/test.sql
!

cd /home/user/reports
uuencode test_output.txt test_output.txt| mailx test.qa@xyz.com



test.sql
-------
SET NEWPAGE NONE
SET LINESIZE 350
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
set TERMOUT off
set trimspool on

spool /home/user/reports/test_output.txt

select distinct
trim(to_char(senderid))||'|'||
trim(s.businessname)||'|'||
trim(DESTINATION)||'|'||
trim(emailaddress)
from email_info ;

spool off;

Very helpful!